Array formulas with AND and OR logic

In an earlier video, I showed how AND logic corresponds to multiplication and OR logic corresponds to addition.

Let’s look at how to apply this in an array formula.

I’ll work through the problem step-by-step.

In column H, I’ll test if state is equal to “tx”.

In column I, I’ll test if the amount is greater than 125.

Now, because AND logic requires multiplication, I’ll multiply these results together in column J.

Notice we only get 1s where the state is “tx” and the amount greater than 125.

All other cases are zero.

The only values that survive are those where both conditions are TRUE.

If I sum the result, we get $500.

We can put this logic in an all-in-one formula.

Notice the logic is identical.

Columns H and I contain the same formulas we’ve already seen.

In column J, since OR logic requires addition, I’ll add these columns together.

Notice we get 2 when both conditions are true.

In column K, I multiply column J by the amounts.

The math operation automatically coerces the TRUE and FALSE values in column J to 1s and 0s.

Again, column J works like a filter, only allowing values that meet criteria.

If I sum the results in column K, we get $1250.

The all-in-one formula uses the same logic.

Then we multiply by the amounts.

Finally, you might wonder why we aren’t using theANDandORfunctions directly to get these same results.

The problem is that these functions are meant toaggregateresults.

In other words, if you give them an array, you’ll get back a single result.

This won’t work when you’re processing multiple values in an array formula.