Page 299 - Excel 2007 Bible

19_044039 ch14.qxp 11/21/06 11:06 AM Page 256
Part II
Working with Formulas and Functions
Using And criteria
An And criterion counts cells if all specified conditions are met. A common example is a formula that
counts the number of values that fall within a numerical range. For example, you may want to count cells
that contain a value greater than 100 and less than or equal to 200. For this example, the new COUNTIFS
function will do the job:
=COUNTIFS(Amount,”>100”,Amount,”<=200”)
If the data is contained in a table, you can use the new Excel 2007 method of referencing data
NOTE
NOTE
within a table. For example, if the table is named Table1, you can rewrite the preceding for-
mula as:
=COUNTIFS(Table1[Amount],”>100”,Table1[Amount],”<=200”)
This method of writing formulas does not require named ranges.
The COUNTIFS function accepts any number of paired arguments. The first member of the pair is the range
to be counted (in this case, the range named Amount); the second member of the pair is the criterion. The
preceding example contains two sets of paired arguments and returns the number of cells in which Amount
is greater than 100 and less than or equal to 200.
Prior to Excel 2007, you would need to use a formula like this:
=COUNTIF(Amount,”>100”)-COUNTIF(Amount,”>200”)
The formula counts the number of values that are great than 100 and then subtracts the number of values
that are greater than or equal to 200. The result is the number of cells that contain a value greater than 100
and less than or equal to 200. This formula can be confusing because the formula refers to a condition
“>200” even though the goal is to count values that are less than or equal to 200. Yet another alternate
technique is to use an array formula, like the one that follows. You may find it easier to create this type of
formula:
{=SUM((Amount>100)*(Amount<=200))}
NOTE When you enter an array formula, remember to use Ctrl+Shift+Enter and don’t type the
NOTE
brackets.
Sometimes, the counting criteria will be based on cells other than the cells being counted. You may, for
example, want to count the number of sales that meet the following criteria:
n Month is January, and
n SalesRep is Brooks, and
n Amount is greater than 1000
The following formula (for Excel 2007 only) returns the number of items that meets all three criteria. Note
that the COUNTIFS function uses three sets of pairs of arguments.
=COUNTIFS(Month,”January”,SalesRep,”Brooks”,Amount,”>1000”)
An alternative formula, which works with all versions of Excel, uses the SUMPRODUCT function. The fol-
lowing formula returns the same result as the previous formula.
=SUMPRODUCT((Month=”January”)*(SalesRep=”Brooks”)*(Amount>1000))
256