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. In the first worksheet, we want to sum orders where the state is Texas or “tx” and the amount is greater than 125. 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. In column K, I’ll multiply column J by the amounts in column C. You can see that column J works like a filter. 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. We are simply multiplying the expressions from column H and column I together, then multiplying that result by column C. Now let’s look at OR logic. Here we want to sum orders where the state is “tx” OR the amount is greater than 125. 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. Now, I need a boolean result, so I’ll force a TRUE or FALSE by checking if the result is greater than zero. 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. After we add the two conditions together, we need to check the results against zero to force a boolean result. Then we multiply by the amounts. Finally, you might wonder why we aren’t using the AND and OR functions directly to get these same results. The problem is that these functions are meant to aggregate results. 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
