Microsoft Office Tutorials and References
In Depth Information
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.
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
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 — This is the range of cells you want evaluated.
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 — 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 — If cells in the average_rangeare empty or contain
text or TRUE/FALSE, they are ignored in the calculation of average.