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 do so this is to use the double negative.

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

Now if I check the array values inside SUM, we see only 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 build 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 of 1s and 0s, 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.