Exceljet

Quick, clean, and to the point

Boolean operations in array formulas

In this video, we'll look at why boolean operations are important in array formulas.

Boolean operations are a key building block in the world of dynamic array formulas.

To illustrate, let's look at some simple order data.

Given the data shown, how can we total orders from Texas using an array formula?

To start off, I'll enter an expression that tests the state column in the data to see if it equals "TX":

=E5:E20="tx"

Notice Excel by default is not case-sensitive.

Because I've provided a range of values from the State column, we get all results in a single dynamic array.

TRUE values indicate orders where the state is "tx" or Texas. FALSE values represent any other state.

Now, as we've seen before, if I try to add up these results with the SUM function, we get zero. This is because the SUM function simply ignores the logical values TRUE and FALSE.

We need to give Excel a little kick to coerce the  TRUE and FALSE values to 1 and 0.

A simple way to so this is to use the double negative.

=--(E5:E20="tx")

Now if I check the array values inside SUM, we see 1s and 0s, and SUM gives us an accurate count of orders from Texas.

Next, let's adjust the formula to sum the value of all Texas orders.

We already have a boolean operation that tests for Texas, so we can built on that directly.

To add order values to the mix, I just need to multiply the original array operation by the array of values.

When I press enter, we get a correct result.

=(E5:E20="tx")*C5:C20

So, why does this work?

We know the first array is already a set 1s and zeros, and the second array is just values. When these two arrays are multiplied together, only values associated with Texas remain.

The other states are now zero.

In fact, we don't actually need the double negative anymore because  any math operation will cause Excel to convert TRUE and FALSE values to 1s and 0s.

When I remove the double negative, we get the same result.

=(E5:E20="tx")*C5:C20

Notice the total in J5 is now the value of all orders from Texas.

Now that we've tested and proved the concept, I can convert the formula to an all-in-one formula that doesn't use the helper column.

=SUM((E5:E20="tx")*C5:C20)

And if I check the value inside sum, we see the same final array.

This is just a taste of how boolean operations are used in array formulas. We'll look at many more examples in upcoming videos.

Dynamic Array Formulas are available in Excel 365 only.
Author 
Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.