Excel Annoyances

COUNTING AND CONDITIONAL SUM ANNOYANCES

SUM VALUES THAT MEET A CRITERION

The Annoyance:

I own a computer-building company, and although most of my orders are less than $5,000, I do get a few that total more than $10,000. I’d like to know how much of my revenue comes from the big orders—which I guess means figuring out what percentage of my orders are for $10,000 or more. Is there some way to find the sum of all my orders for amounts of $10,000 or more, and then use that sum to determine the percentage they contribute to my revenue?

The Fix:

You can find the sum of cells that meet any given criterion by using a SUMIF formula. To create a SUMIF formula, choose Insert → Function, type SUMIF in the “Search for a function” pull down, and click OK to display the Function Arguments dialog box for the SUMIF function (shown in Figure 3-25). With Excel 2000 and 97, select All in the “Function category” list and then SUMIF in the “Function name” list, and click OK to get the Function Arguments dialog box.

Figure 3-28. The SUMIF Function Arguments dialog box lets you define the rules that your data must meet to be considered in the sum.

In the Range field, type the name of the range you want to evaluate in the formula, type the range’s cell addresses, or click the Collapse Dialog button at the far right in the field and select the cells. Then, in the Criteria field, type in the ...