My Newest Book
Excel 2003 Study Guide

get it at Amazon.

FROM WILEY: Download sample Excel files to accompany the book.
Instructors may download tests, slides, outlines, etc. HERE

Linda's Ebooks

Linda's CD

The Newbie Club

Online classes
at Eclectic Academy

FrontLook
Add-ins
& Screen Capture

WinBackup
SpeedUpMyPC
WinTasks5Pro

Array-Entered Formulas in Excel

by Linda Johnson

reprinted with permission from TechTrax

AFs (array-entered formulas) are a feature of Excel that is not covered well in Excel's help menus. Actually, until Excel 2002, they were hardly mentioned in there at all. And, the books I have seen that address AFs would intimidate the average user into believing this isn't something they could even attempt. So, my intention here is to tell you what AFs can do and how to use them in the simplest way possible. Once you understand the basics of how these work, you can then try them on more complicated data.

First of all, why would we want to use them? Here's a scenario. Let's say you have a list of numbers in Column A and you want a sum or a count of them based on two different criteria. You can use a SUMIF or a COUNTIF formula if you just have one criterion; but if you have more than one, you will need to use an AF.

Let's do this together, with a simple example. Enter the following data into Column A in a brand new, empty workbook.

	A
1	Salary
2	$500
3	$1000
4	$200
5	$600
6	$1200
7	$100
8	$5000
9	$1700
10	$400
11	$500

If you wanted a sum of all the amounts that are greater than $500, you would use the formula:

=SUMIF(A2:A11,">500")

...which would give you the result of $9500.

If you wanted to get a count, instead of adding them together, you would use the formula:

=COUNTIF(A2:A11,">500")

... which would give the result of 5.

But, let's say you need two criteria applied. The SUMIF and COUNTIF formulas are limited here.

Important Note! First, let me explain how an AF must be entered. All AFs have curly brackets { } around them. This is how Excel identifies them as AFs. However, you do not type in the curly brackets! These are added when you hit ctrl+shift+enter and you must do this every time you enter or change an AF. If you manually type in the curly brackets, Excel does not recognize it as being a formula.

Let's Try It
Say you want a sum of all of the numbers greater than 500 and less than 300. Here's where an AF comes in really handy.

Click into cell A12 and type this formula and when you are finished typing, hit ctrl+shift+enter:

=SUM((A2:A11>500)*(A2:A11))+SUM((A2:A11<300)*(A2:A11))

You will see that you get $9800 as a result and this is correct. And, if you look at the formula in the Formula Bar at the top of your spreadsheet, you will see that Excel added the curly brackets to the formula and it looks like this:

{=SUM((A2:A11>500)*(A2:A11))+SUM((A2:A11<300)*(A2:A11))}

TIP! If your formula gives you an error or you are not getting the correct answer, and you are sure you hit ctrl+shift+enter, it's possible that one of the values in A2:A11 is not being seen correctly by Excel. To help troubleshoot your problem, you can display what Excel is calculating in any part of your formula. When you use AFs, Excel looks into each area nested in parentheses and calculates that first. So, in the example above, A2:A11>500 would return either a true or a false. Is it greater than 500 or isn't it?

To check, highlight A2:A11>500 (but not the parentheses around it), as in the image below.

Then, hit your F9 key and you will see what Excel is seeing:

This is a handy tip to remember for troubleshooting the parts of any formula that are giving you problems. Once you have seen the True/False results, hit your Esc (Escape) key to exit the formula. Hitting Enter will not work, because Excel will think you are correcting the formula and remember that any time you correct an AF, you must hit ctrl+shift+enter.

So, that's how to do a simple sum AF. Now, what if we wanted a count instead of a sum? That's easy to fix. All you need to do is replace the part of the formula that multiplies the cells by themselves, with a one (1).

Just adjust the above formula to look like this, then hit ctrl+shift+enter again to update it:

=SUM((A2:A11>500)*1)+SUM((A2:A11<300)*1)

The result you get should be 7. If it's not, recheck your formula and try the F9 trick mentioned above.

Here's another scenario where an AF comes in handy. Let's say you want to sum or count the contents of Column A, based on something that is in another column. Yup, AFs can do this too and boy, is this ever handy!

Add this data to Column B:

	A	B
1	Salary	Dept.
2	$500	A
3	$1000	B
4	$200	C
5	$600	D
6	$1200	C
7	$100	B
8	$5000	A
9	$1700	A
10	$400	A
11	$500	C

Here are some formulas you can try to see how this works. These formulas include the curly brackets, but remember, you do not type them, just hit ctrl+shift+enter to let Excel add them for you:

Total salaries for the people in Dept. A:

{=SUM((A2:A11)*(B2:B11="A"))}

...the result should be $7600

Count of all people in Dept. C who earn less than $1000:

{=SUM((A2:A11<1000)*(B2:B11="C"))*1}

...the result should be 2

Average salary for people who make more than $1000:

{=AVERAGE(IF(A2:A11>1000,A2:A11,""))}

...the result should be $2663.33

Getting the hang of it? I know it's a lot to grasp, but I recommend you think of various scenarios and try them out. The best way to learn this stuff is to play with it.

Have fun!