Hello,
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.
e.g.
SUMIF(B1,"red",A1)
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.
e.g.
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.
e.g.
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.)
e.g.
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.)
Thanks.