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
11.32 .
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.
Search JabSto ::




Custom Search