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))

Share this:

Like this:

2 Responses to “How do ARRAY Formulas Work?”

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.