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>
Click on the links at the bottom of the page to download the Excel file to accompany this page and the PDF version of this page.
I have used the example from one of Chip Pearson’s web pages but have created my own explanations as I think they are easier to follow than Chip’s: http://www.cpearson.com/excel/ArrayFormulas.aspx
Duncan Williamson
Click here to download the Excel file that accompanies this post: why_arrays_work
Click here to download the PDF version of this post: why_array_formulas_work
