How can i use the sumif function on multiple references.
GROUP COLOUR TOTAL
GROUP A BLUE 50
GROUP A BLUE 100
GROUP A GREEN 25
GROUP A YELLOW 50
GROUP A GREEN 35
GROUP B GREEN 45
GROUP B RED 30
GROUP B RED 5
GROUP B BLUE 7
GROUP A BLUE 65
GROUP A YELLOW 88
GROUP A GREEN 10
GROUP B BLUE 45
GROUP B RED 56
GROUP A RED 89
GROUP A YELLOW 100
GROUP A PURPLE 1
GROUP B PURPLE 30
GROUP B PURPLE 45
I want to count the total (From the total column) against each colour, however, also against the group.
I created a new table, which removes the duplicate colours & next to this i want to include the total of each colour against each group. So the results would look like....
COLOUR Group A Group B
BLUE 215 52
GREEN 70 45
YELLOW
RED
PURPLE
Hope that makes sense. Any help would be gratefully appreciated.
Thank you
The formula you're looking for is SUMIFS(), it's perfect for this case. I put Group in A1:A19, Colour in B1:B19, and Total in C1:C19
Answer table was laid out from A24:C29
=SUMIFS($C$1:$C$19,$B$1:$B$19,$A25,$A$1:$A$19,B$24)
The way this works, first it requests the Sum Range, that would be your Total Column. Next it will want the first criteria range, I put in colour. It will then request the first criteria. I selected the first colour. The same process is completed for each new criteria until they have all been included.
It's important with this formula that the criteria range be of the same size as the sum range. Otherwise you will get an error.