Microsoft Office Tutorials and References

In Depth Information

**Syntax**

Mastering the SUMIF and COUNTIF functions invariably leads to more

questions about doing more powerful versions. If you need to sum based on

more than one condition, you can use DSUM, SUMPRODUCT, or SUMIFS. The

SUMIFS function is discussed in the next section.

Note

An interesting variation on the SUMIF, AVERAGEIF, and COUNTIF

functions is worth mentioning. It is possible to build the criteria ar-

gument on the fly. To count records that are above average, you can

use =COUNTIF(H11:H5011,">"&AVERAGE(H11:H5011)).

Syntax

=SUMIF(range,criteria,sum_range)

Syntax

=AVERAGEIF(range,criteria,average_range)

The SUMIF function adds the cells specified by a given criteria. The

AVERAGEIF function averages the cells specified by a given criteria. Occa-

sionally, the range you want to search is also the range to sum. For example,

perhaps your criteria is to look for rows where the revenue is greater than

100,000. In this case, because your range to add is the same as your range to

search, you can leave off the third argument, as shown in cell H2 of
Figure

The SUMIF function takes the following arguments:

•
range

range
—
This is the range of cells you want evaluated.

•
criteria

criteria
—
This is the criteria in the form of a number, an expression,

or text that defines which cells will be counted. For example, criteria

can be expressed as 32, "32", ">32", or "apples".

•
sum_range

sum_range
—
This is the range of cells to sum. The cells in sum_range

are summed only if their corresponding cells in rangematch the cri-

teria. If sum_rangeis omitted, the cells in rangeare summed.

•
average_range

average_range
—
If cells in the average_rangeare empty or contain

text or TRUE/FALSE, they are ignored in the calculation of average.