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

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: