EXCEL - SUMIFS up 2 dimensional range

Question

I'm trying to sum up a range of columns AND rows in EXCEL/*

Trivial Data example:

A	B	C	D	E	F
Colour	Size	Jan	Feb	Mar	Apr
red	S	1	0	2	0
red	M	3	2	1	3
green	M	4	3	5	3
geren	L	1	0	0	2
blue	S	2	1	2	1

Desired Outcome:

Color	Small	Medium/Large
red	3	9
green	0	18
blue	6	0

I've tried for the red/small box:

=SUMIFS(C2:F5, A2:A5, "red", B2:B5, "S")

but it's returning a #VALUE error.

If the sum range is C2:C5 it works and returns a 1 but:

1. I need all the columns
2. The order of the rows is HIGHLY variable
3. The number of columns is controlled by a variable so I actually have an INDEX in there that I've excluded from this because it would just confuse things.

How can I get this to work?

/* I have inherited this process and I'm not allowed to use anything fancier than EXCEL formula

Answer 1

You need to switch to SUMPRODUCT in such cases:

=SUMPRODUCT((A2:A5="red")*(B2:B5="S")*C2:F5)

Or even just SUM:

=SUM((A2:A5="red")*(B2:B5="S")*C2:F5)

though the latter may require committing with CTRL+SHIFT+ENTER, depending on your version of Excel.