Microsoft Office Tutorials and References

In Depth Information

**Advanced Counting Formulas**

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 example above 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”)

This formula counts the number of values that are greater than 100 and then subtracts the

number of values that are greater than 200. The result is the number of cells that contain a value

greater than 100 and less than or equal to 200.

Creating this type of 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. An alternate technique

is to use an array formula, such as the one that follows. You may find creating this type of

formula easier.

{=SUM((Amount>100)*(Amount<=200))}

When you enter an array formula, remember to use Ctrl+Shift+Enter — and don’t type

the 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:

h
Month is January,
and

h
SalesRep is Brooks,
and

h
Amount
is greater than 1,000

The following formula returns the number of items that meet 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 versions prior to Excel 2007, uses the SUMPRODUCT

function. The following formula returns the same result as the previous formula:

=SUMPRODUCT((Month=”January”)*(SalesRep=”Brooks”)*(Amount>1000))