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

1. ## 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

2. 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. 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

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

pjscott90 (2015-05-25)

6. Thanks very much!