The life of a consultant pretty much evolves around three Microsoft applications: Power Point, Word, and Excel. Other than e-mail and the web browser, it is rare that I start any other program on my laptop. The result is that you tend to become reasonably familiar with these programs, and it is not often that I learn anything new about them.
But I found a cool trick that I didn’t know. The SUMIF function allows you to do conditional array operations, but it is limited to a single condition and the sum function. Often you need more.
Consider the following example where we are keeping track of our daily fruit consumption, and want to calculate our weekly spend per type of fruit.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Day | Fruit | Quantity | Price |
| 2 | Monday | Apple | 1 | £0.75 |
| 3 | Tuesday | Pear | 2 | £1.10 |
| 4 | Wednesday | Apple | 1 | £0.80 |
| 5 | Thursday | Apple | 3 | £0.70 |
| 6 | Friday | Orange | 2 | £0.50 |
It is clear that we spent £3.65 on apples this week, but how do we extend the sheet to hold this information like this:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Day | Fruit | Quantity | Price |
| 2 | Monday | Apple | 1 | £0.75 |
| 3 | Tuesday | Pear | 2 | £1.10 |
| 4 | Wednesday | Apple | 1 | £0.80 |
| 5 | Thursday | Apple | 3 | £0.70 |
| 6 | Friday | Orange | 2 | £0.50 |
| 7 | ||||
| 8 | Weekly spend: | |||
| 9 | Apple | £3.65 | ||
| 10 | Orange | £1.00 | ||
| 11 | Pear | £2.20 |
With my new-found knowledge this is really easy, and D9 contains the formula
SUMPRODUCT((B$2:B$6=B10)*1, C$2:C$6, D$2:D$6)
which neatly does the trick. The expression (B$2:B$6=B10) returns an array of boolean values indicating if the cells in B$2:B$6 contains the string Apple. In other words, the array {True, False, True, True, False}. I multiply it with 1 to turn it into a numeric array of {1, 0, 1, 1, 0} which I can then use in the SUMPRODUCT formula.