SUMIFS with multiple values for one criteria range

**Harsha Haridas** · 05-07-2009, 07:55 AM

I am using Excel 2007 to analyse soem data. I need to get the total amount based on some criteria. I know SUMIFS can be used for this. Please see the attached excel sheet. I am using the below formula to calculate the total amount.
=SUMIFS(C2:C16,A2:A16,"Jan",B2:B16,"Fri")+SUMIFS(C2:C16,A2:A16,"Jan",B2:B16,"Mon")+SUMIFS(C2:C16,A2: A16,"Feb",B2:B16,"Fri")+SUMIFS(C2:C16,A2:A16,"Feb",B2:B16,"Mon")

This is only an example. The actual calculation I am trying to do involve more than 500 rows. So I need a better solution.

This is what i am looking for:
SUM Amount if ( (Month is equal to 'Jan' or 'Feb') AND (Day is equal to 'Mon' or 'Fri')

That is, a formula in which i can give multiple values for one range. Can some one help me with an Excel 2007 formula for this?

**zbor** · 05-07-2009, 08:07 AM

hOW ABOUT SOMETHING LIKE THIS?

SUMIFS(1).xlsx

Ups.. caps lock

**martindwilson** · 05-07-2009, 08:13 AM

i believe you can do up to 27 criteria
so perhaps
sumifs(C2:C16,A2:A16,"Jan",B2:B16,"Fri",A2:A16,"feb",B2:B16,"Fri",A2:A16,"Jan",B2:B16,"mon",A2:A16," feb",B2:B16,"mon")

**daddylonglegs** · 05-07-2009, 08:39 AM

That will give you a sum of zero Martin, because all criteria need to be satisfied. Using SUMPRODUCT

=SUMPRODUCT(C2:C16,--ISNUMBER(MATCH(A2:A16,{"Jan","Feb"},0)),--ISNUMBER(MATCH,B2:B16,{"Fri","Mon"},0)))

**Harsha Haridas** · 05-07-2009, 09:14 AM

Tried the below formula suggested.
=SUMPRODUCT(C2:C16,--ISNUMBER(MATCH(A2:A16,{"Jan","Feb"},0)),--ISNUMBER(MATCH,B2:B16,{"Fri","Mon"},0)))

But I am getting an error "You have entered too many arguments for this function".

**martindwilson** · 05-07-2009, 09:21 AM

missing a "("

=SUMPRODUCT(C2:C16,--ISNUMBER(MATCH(A2:A16,{"Jan","Feb"},0)),--ISNUMBER(MATCH(B2:B16,{"Fri","Mon"},0)))

**daddylonglegs** · 05-07-2009, 09:37 AM

Apologies,

as Martin says, missing parenthesis, thanks Martin

I ignored the golden rule....."Always test your formulas"

**Harsha Haridas** · 05-07-2009, 10:51 PM

Thanks a lot. This worked.

One more query: Can I use wild card serch (instead of "Jan", can I use "J*" so that I get values corresponding to Jan, June and July) in SUMPRODUCT?

**martindwilson** · 05-09-2009, 06:09 AM

TRY
=SUMPRODUCT(C2:C16,--ISNUMBER(SEARCH("J*",A2:A16)),--ISNUMBER(MATCH(B2:B16,{"Fri","Mon"},0)))

**koekert** · 01-27-2012, 05:08 AM

Although this thread is already quite for some time, I try to get a solution for the following challenge. I try to sum the values based on multiple criteria and criteria ranges using the following formula:

=IF(F$3="Yes", SUMIFS(Backlog!$F:$F, Backlog!$C:$C, "Task", Backlog!$E:$E, "Unplanned", Backlog!$H:$H, Sprint_ID, Backlog!$J:$J, 'Burn down'!F$2), NA())

Now what I would like to add is a check for the range Backlog!$I:$I being equal to either "Added"or "Done". Using the example above results in creating the following formula:
=SUMPRODUCT(Backlog!$F:$F, --(Backlog!$C:$C = "Task"), --(Backlog!$E:$E="Unplanned"), --(Backlog!$H:$H=Sprint_ID), --(Backlog!$I:$I={"Done","Added"}), --(Backlog!$J:$J= 'Burn down'!F$2))

Although is not yet equal to the SUMIFS as shown earlier (still need to add the precondition), the formula returns me a #value

When I change the condition --(Backlog!$I:$I={"Done","Added"}) to --(Backlog!$I:$I={"Done",}) the formula returns me a number (which is expected) but now is doing basically the same as the SUMIFS shown earlier.

How can I make this formula to work with both conditions?

Thank you!