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?
TRUE values indicate orders where the state is “tx” or Texas.
FALSE values represent any other state.
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.
Next, let’s adjust the formula to sum thevalueof all Texas orders.
We already have a boolean operation that tests for Texas, so we can build on that directly.
When I press enter, we get a correct result.
So, why does this work?
When these two arrays are multiplied together, only values associated with Texas remain.
The other states are now zero.
When I remove the double negative, we get the same result.
Notice the total in J5 is now the value of all orders from Texas.
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.
