Let’s say you have some data like, oh I don’t know,
First Name, Last Name, Gender, State, and some number.
You could add up the numbers for all the males in Texas with an array formula.
If you didn’t know that, go read Anatomy of an Array Formula. One of my workshop attendees had this situation except that she wasn’t hardcoding the values ‘Male’ and ‘Tx’. Instead she was getting those values from a cell that had a data validation drop down list. Also in that list was the word ‘All’, which of course meant to include everything from that column.
For this example, I set up data validation to include All, Male, and Female.
And I changed my array formula to this
IF statement says: If G3 is All, then return TRUE no matter what’s in that column. If it’s not All, return TRUE for only those rows that match G3.
Normally in array formula, we multiply one array times another. The first element of array 1 is multiplied by the first element of array 2. The second element of array 1 is multiplied by the second element of array 2. And so on for every element. When we multiply an array by a scalar value (like TRUE in this example), then each element of the array is multiplied by that scalar value. That allows us to return TRUE for every Gender, thereby ignoring that column.