Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    May 2015
    Posts
    2
    Thanks
    2
    Thanked 0 Times in 0 Posts

    EXCEL: How do I use 'SUMIFS' with multiple criteria but a 'sumrange' of multiple columns.

    I have the following (simplified) table:
    I am trying to sum the last three columns (collectively know as total cases) if the following conditions are met: the year is 2009, the vaccine status is 1 and the inclusion is 1.

    the formula i'm using is =SUMIFS(E2:G9511,D2:D9511,"=1",B2:B9511,"=2009",C2 :C9511,"=1")

    This returns a #value! error.

    Can anyone help me out?

    A bit more background - there are 9510 rows of areas of the country with cases from years 2009-2013, vaccinated or not, included in the study or not, and there are further columns (left our here for simplicity) for population of each area and age of the children. Essentially I am working out the incidence of disease per area per year, per age and per vaccine status. I am not able to add a column (e.g. a total cases column) as the data is server hosted.

    Many thanks
    Last edited by RetiredGeek; 2015-05-22 at 13:12. Reason: Added NoParse Tags

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    pjscott,

    in column H you could sum the 3 adjacent cells using =SUM(E4:G4) and copy down then use the formula

    Code:
    =SUMIFS(H4:H9511,D4:D9511,1,B4:B9511,2009,C4:C9511,1)
    to perform the sumifs

    or sum Product

    Code:
    =SUMPRODUCT((E4:G9511)*((B4:B9511=2009)*(C4:C9511=1)*(D4:D9511=1)))
    HTH,
    Maud

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    pjscott90 (2015-05-25)

  4. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi Maud

    I like your SUMPRODUCT method best!

    Code:
    =SUMPRODUCT((E2:G9511)*((B2:B9511=2009)*(C2:C9511=1)*(D2:D9511=1)))
    ..if you want to start from row2

    ..and welcome to this Lounge PJ

    zeddy
    Last edited by zeddy; 2015-05-22 at 12:54.

  5. The Following User Says Thank You to zeddy For This Useful Post:

    pjscott90 (2015-05-25)

  6. #4
    New Lounger
    Join Date
    May 2015
    Posts
    2
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks very much!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •