# 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.