Combining several SUMIFs in a single formula?


I’m trying to use a formula to get a sum from a “Quantity” column in a subtable, based on several criteria in the same column.

That is, for something like this:

A - B - C
Qty - Color - Size
3 - green - large
4 - red - small
5 - blue - small
6 - black - large
7 - red - large

For a sum of all the red items, SUMIF works fine.
The result is 11.

If I want a sum of all the red items that are also small I can use SUMIFS() because the second criterion is from a different column.
SUMIFS (B1,"red",C1,"small",A1)
The result is 4.

However, if I want a sum of all the items that are red and all the items that are green, adding the two SUMIF results together doesn’t seem to work.
SUMIF (B1,"red",A1) + SUMIF (B1,"green",A1)
This gives no result, and I don’t understand why not.

Note that using SUMIFS on the same column doesn’t work either (and nor should it.)
SUMIFS (B1,"red",B1,"green",A1)
That is asking for a sum of the quantity of items where each item is both red and green (which is logically impossible.)



I’ve tested here with above settings:

And the result is correct:

Hence, would you mind share some screenshots of your sheet design for us to refer to?

Hello Angie, thank you for your quick response.

I think I found my mistake – I didn’t realize that the formula functions are case-sensitive.

In other words sumif doesn’t work, but SUMIF does.

My apologies for the error.

1 Like