## Why do ARRAY Formulas Work?

ARRAY formulas can be a very simple solution to a complex situation but many people do not consider using them because they don’t understand how and why they work. This post illustrates why ARRAY formulas work: it is all down to true and false logic. Work through the three examples included here and then ask me for the spreadsheet I have already prepared and you can see two more examples.

For example, in the case of the following table of information found in the range A4:C13

 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

We might want to find out the total units sold by a particular salesman … In the spreadsheet file that accompanies this post, I have programmed =SUM() as an ARRAY formula to find, for example, how many faxes salesman Brown has sold:

In cell C16 you will find the following ARRAY formula:

Summing Sales: Faxes Sold By Brown

=SUM((A5:A13=”Fax”)*(B5:B13=”Brown”)*(C5:C13)) … <Ctrl>+<Shift>+<Enter>

The following schedule illustrates why this ARRAY entered formula works:

A x B x C = Result

1 x 1 x 1 = 1 … A5 contains Fax = TRUE, B5 contains Brown = TRUE, C5 contains a value

0 x 0 x 10 = 0 … A6 contains Phone = FALSE, B6 contains Smith = FALSE, C6 contains a value

1 x 0 x 20 = 0 … A7 contains Fax = TRUE, B7 contains Jones = FALSE, C7 contains a value

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

Secondly, we might just want to know how many times Brown has sold one or more faxes, in cell C19 you will find the following ARRAY formula:

Logical AND (Faxes AND Brown)

=SUM((A5:A13=”Fax”)*(B5:B13=”Brown”)) … <Ctrl>+<Shift>+<Enter>

The following schedule illustrates why this ARRAY entered formula works:

A x B x C = Result

1 x 1 = 1 … A5 contains Fax = TRUE AND B5 contains Brown = TRUE

0 x 0 = 0 … A6 contains Phone = FALSE, B6 contains Smith = FALSE

1 x 0 = 0 … A7 contains Fax = TRUE, B7 contains Jones = FALSE

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

Thirdly, A logical OR operation can be accomplished with addition. For example,

=SUM(IF(((A2:A10=”Fax”)+(B2:B10=”Jones”))>0,1,0))

will count the number of sales (not the number of units sold) in which the product was a Fax OR the salesman was Jones (or both). In cell C22 you will find the following ARRAY formula:

Logical OR (Faxes Or Jones)

=SUM(IF((A5:A13=”Fax”)+(B5:B13=”Jones”),1,0)) … <Ctrl>+<Shift>+<Enter>