# Anatomy of an Array Formula

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
`=SUM((C2:C9="Tom")*((D2:D9=1)+(D2:D9=2))*(E2:E9))`

Step 2: D2:D9 are evaluated into its values
`=SUM((C2:C9="Tom")*(({1;1;2;2;2;3;3;3}=1)+({1;1;2;2;2;3;3;3}=2))*(E2:E9))`

Step 3: The equality comparison is performed and a TRUE or FALSE is returned for each element of the array
`=SUM((C2:C9="Tom")*(({TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})+({FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE}))*(E2:E9))`

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
`=SUM((C2:C9="Tom")*({1;1;1;1;1;0;0;0})*(E2:E9))` `TRUE + FALSE = 1 + 0 = 1`

`FALSE + FALSE = 0 + 0 = 0`

Step 5: C2:C9 is evaluated into its values
`=SUM(({"Tom";"Dick";"Tom";"Dick";"Harry";"Tom";"Dick";"Harry"}="Tom")*({1;1;1;1;1;0;0;0})*(E2:E9))`

Step 6: That equality comparison is performed
`=SUM(({TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE})*({1;1;1;1;1;0;0;0})*(E2:E9))`

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
`=SUM({1;0;1;0;0;0;0;0}*(E2:E9))`

Step 8: E2:E9 is evaluated into its values
`=SUM({1;0;1;0;0;0;0;0}*({12200;7100;10600;9500;17300;15000;10300;16800}))`

Step 9: The two arrays are then multiplied together
`=SUM({12200;0;10600;0;0;0;0;0})`

Step 10: The SUM operation is performed on the remaining array
`22800`

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.

## 11 thoughts on “Anatomy of an Array Formula”

1. Andrew says:

Dick, I remember asking about why arrays worked like they do on a forum a while back. Nobody answered my question at the time but you sure have. Thanks bud.

2. Jan Nordgreen says:

Thanks for a nice blog! I enjoy the down to earth style.

Maybe your readers will enjoy my article ‘Ignorance – an excellence starting point for learning’ where array formulas play a leading role. It is at http://mumnet.tripod.com/thoughts/ignorance.htm.

3. John Mansfield says:

An excellent explanation. Appreciate it.

4. Ola Sandström says:

They will give the same answer.

ABCD
1ExcelWordPPT
2Apple123
3Orange456
4Kiwi789
5
6Orange
7Word

Normal:
=INDEX(B2:D4;
MATCH(A6;A2:A4;0);
MATCH(A7;B1:D1;0))

Array formula:
{=SUM(
(A2:A4=A6)*
(B1:D1=A7)*
(B2:D4))}

Ola

5. Jamie Collins says:

It’s worth pointing out that if you want to evaluate parts of a formula as Dick has done e.g. convert

D2:D9

into the array of values

{1;1;2;2;2;3;3;3}

simply highlight the relevant part and press F5. Don’t forgot to press escape (or equivalent) when done or the evaluated text will remain in the formula.

Jamie.



6. Jamie Collins says:

… that should be F9, of course.

7. David Shipp says:

I haven’t had a close look at what you have written but think I use pivot tables to obtain similar results. I do have a complex problem, however, and wondered if you might suggest if arrays could be a way of solving it.

If there is a lottery in which 7 winning numbers and 2 supplementary numbers are drawn from 45 numbers, given these numbers and 8 “chosen” numbers, what are all the unique combinations of 7 possible (there should be 8)? In addition, what is the highest combination on winning and supplementary numbers for each unique combination?

Further again, what if we want to look at 9 “chosen” numbers (a system “9?) or 10 (a system “10?)? Can we use Excel arrays to extract this information?

8. HARINDER BHATTI says:

VERY NICE EXPALANATIONS.

Posting code? Use <pre> tags for VBA and <code> tags for inline.