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.

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.

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.

Hey Dick, don’t forget MVP Bob Umlas’ white paper on array formulas:

http://www.emailoffice.com/excel/arrays-bobumlas.html

An excellent explanation. Appreciate it.

I have not thought about this before.

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

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.

… that should be F9, of course.

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?

VERY NICE EXPALANATIONS.