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.

ABCD
1DayFruitQuantityPrice
2MondayApple1£0.75
3TuesdayPear2£1.10
4WednesdayApple1£0.80
5ThursdayApple3£0.70
6FridayOrange2£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:

ABCD
1DayFruitQuantityPrice
2MondayApple1£0.75
3TuesdayPear2£1.10
4WednesdayApple1£0.80
5ThursdayApple3£0.70
6FridayOrange2£0.50
7
8Weekly spend:
9Apple£3.65
10Orange£1.00
11Pear£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.