If you’ve never used an array formula, you owe it yourself to read Chip Pearson’s page on array formulas. It’s the best explanation that I’ve seen, and it’s how I learned them.
Let’s get under the hood of array formulas. Take this worksheet example:
The formula in G4 finds the total sales for Tom for months 1 and 2. The braces around the formula tell us that it was array-entered with Control+Shift+Enter, instead of just Enter. Excel converts each array equality into TRUE or FALSE, and in Excel, TRUE = 1 and FALSE = 0. Here’s how this formula breaks down:
Step 1: Start with the original formula
Step 2: D2:D9 are evaluated into its values
Step 3: The equality comparison is performed and a TRUE or FALSE is returned for each element of the array
Step 4: When the two arrays of TRUES and FALSES are combined with the + operator, Excel converts the TRUES and FALSES to 1’s and 0’s, then adds them
Step 5: C2:C9 is evaluated into its values
Step 6: That equality comparison is performed
Step 7: The first array of TRUES and FALSES is multiplied to the second array of 1’s and 0’s. Remember that TRUE = 1 and FALSE = 0
Step 8: E2:E9 is evaluated into its values
Step 9: The two arrays are then multiplied together
Step 10: The SUM operation is performed on the remaining array
Once you understand how the TRUES and FALSES are converted to 1’s and 0’s, then combined with * representing AND and + representing OR, then writing array formulas will be a breeze. Hopefully this got you a little closer.