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

11 12 2012 at 8:53 am

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

14 09 2014 at 12:48 pm

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