Excel Tip: Array boolean operator

On , wrote in CYBAEA Journal:

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. (You can download the Excel spreadshet here.)

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 (as you can easily see in the spreadsheet)

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.

I have not been able to find any documentation on this feature. It does not work in version 1.4 of OpenOffice which simply barfs #VALUE!, but works perfectly in the Gnumeric spreadsheet application.

Last modified .