Microsoft Office Tutorials and References

In Depth Information

**SUMIF and COUNTIF**

You can also use MIN and MAX with a range, as shown here:

=MAX(A1:A12)

or with more than one range, such as this:

=MAX(A1:A12, B1:B12)

SUMIF and COUNTIF

SUMIF and COUNTIF, respectively, sum or count values if a supplied criterion

is met. This makes for some robust calculations. With these functions it’s

easy to return answers for a question such as “How many shipments went

out in October?” or “How many times did the DJIA go over 10,000 last year?”

SUMIF takes three arguments:

✓
A range in which to apply the criteria

✓
The actual criteria

✓
The range from which to sum values

A key point here is that the first argument may or may not be the same range

from which values are summed. Therefore, you can use SUMIF for a question

such as “How many shipments went out in October?” but also for one such as

“What is the sum of the numbers over 100 in this list?” The syntax for SUMIF

follows:

=SUMIF(range,criteria,sum_range)

Note too that the third argument in SUMIF can be left out. When this

happens, SUMIF uses the first argument as the range in which to apply the

criteria and also as the range from which to sum.

COUNTIF counts the number of items in a range that match criteria. This is

just a count. The value of the items that match the criteria doesn’t matter

past the fact that it matches the criteria. But after a cell’s value matches the

criteria, the count of that cell is 1. COUNTIF takes just two arguments:

✓
The range from which to count the number of values

✓
The criteria to apply

The syntax for COUNTIF follows:

COUNTIF(range,criteria)