How do ARRAY Formulas Work?

An ARRAY formula is created by building a series of arguments and then rather then just pressing , you press ++. I assume, for the purposes of this page that you are not new to these formulas. You are here, then, to understand how these unusual formulas work.

Chip Pearson has an excellent site that is worth a visit and on that site he has an ARRAY page that included the following example (see link below):

For the purposes of this page, this table is found in the range A4:C13 in my version of this worksheet.

Product	Salesman	Units Sold
Fax	Brown	1
Phone	Smith	10
Fax	Jones	20
Fax	Smith	30
Phone	Jones	40
PC	Smith	50
Fax	Brown	60
Phone	Davis	70
PC	Jones	80

Chip explains how to set up ARRAY formulas to do the following:

Summing Sales: Faxes Sold By Brown

• Logical AND (Faxes And Jones)
• Logical OR (Faxes Or Jones)
• Logical XOR (Fax Or Jones but not both)
• Logical NAND (All Sales Except Fax And Jones)

I always tell my delegates and readers: there is often more than one way to find a solution with Excel and these examples are no different. We are concentrating on ARRAY formulas here!

Summing Sales: Faxes Sold By Brown … ARRAY enter the following in an empty cell =SUM((A5:A13=”Fax”)*(B5:B13=”Brown”)*(C5:C13))

Logical AND (Faxes And Jones) … ARRAY enter the following in an empty cell
=SUM((A5:A13=”Fax”)*(B5:B13=”Brown”))

Logical OR (Faxes Or Jones) … ARRAY enter the following in an empty cell
=SUM(IF((A5:A13=”Fax”)+(B5:B13=”Jones”),1,0))

Logical XOR (Fax Or Jones but not both) … ARRAY enter the following in an empty cell
=SUM(IF(MOD((A5:A13=”Fax”)+(B5:B13=”Jones”),2),1,0))

Logical NAND (All Sales Except Fax And Jones) … ARRAY enter the following in an empty cell
=SUM(IF((A5:A13=”Fax”)+(B5:B13=”Jones”)2,1,0))

WHY DO THESE ARRAY FORMULAS WORK, THEN?

What follows are my explanations of how two of the above ARRAY formulas work … Chip provides a different approach.

Summing Sales: faxes sold by Brown

=SUM((A5:A13=”Fax”)*(B5:B13=”Brown”)*(C5:C13))

The ARRAY formula starts by looking down column A and when it finds the search string Fax it records 1 otherwise it records 0

The ARRAY formula then looks down column B and records 1 when it finds the string Brown otherwise it records 0

Finally in this example, it then looks down C and multiplies together the values in columns A by B by C
and then adds together all of the values from its calculations … SUM. That is …

A x B x C = Result
1 x 1 x 1 = 1
0 x 0 x 0 = 0
1 x 0 x 20 = 0
1 x 0 x 30 = 0
0 x 0 x 40 = 0
0 x 0 x 50 = 0
1 x 1 x 60 = 60
0 x 0 x 70 = 0
0 x 0 x 80 = 0
sum = 61

Of course, it does all of this in memory, there is no table output, just the final result, 61 in this case

Logical AND (Faxes and Jones)

=SUM(IF((A5:A13=”Fax”)+(B5:B13=”Jones”),1,0))

In this case, we are using a SUM ARRAY formula so why does it essentially COUNT for us?

The ARRAY formula starts by looking down column A and when it finds the search string Fax it records 1 otherwise it records 0

The ARRAY formula then looks down column B and records 1 when it finds the string Brown otherwise it records 0

Finally in this example, it multiplies together the values in columns A by B and then adds together all of the values from its calculations … SUM. That is …

A x B x C = Result
1 x 1 = 1
0 x 0 = 0
1 x 0 = 0
1 x 0 = 0
0 x 0 = 0
0 x 0 = 0
1 x 1 = 1
0 x 0 = 0
0 x 0 = 0
sum = 2

Of course, it does all of this in memory, there is no table output, just the final result, 61 in this case

For descriptions and explanations of the other ARRAY formulas above, see the Chip Pearson page for his explanations:

I hope this helps at least a little bit to explain why ARRAY formulas work.

Duncan Williamson