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

2 Responses to “How do ARRAY Formulas Work?”


  1. Thanks for that comment but in my opinion you have missed the point: I admired Chip’s page but found it too complex for the ordinary user so I SIMPLIFIED it. However, if there are errors that I have not spotted I would be grateful if you could point them out for me, I would truly appreciate it. This is a non commercial blog, in essence, so all assitance is valuable for me.

    Best wishes

    Duncan


  2. Thank you for sharing your info. I truly appreciate your efforts and I am waiting for your further write ups thanks once again.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: