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:
- I need all the columns
- The order of the rows is HIGHLY variable
- 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
CodePudding user response：
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.