Microsoft Office Tutorials and References
In Depth Information
Using selected mathematical functions
SUM, these buttons include a menu of other commonly used functions. If you select a cell
and click the Sum button, Excel creates a SUM formula and guesses which cells you want to
total. To enter SUM formulas in a range of cells, select the cells before clicking Sum.
The SUMIF, SUMIFS, and COUNTIF functions
The SUMIF function is similar to SUM, but it first tests each cell using a specified
conditional test before adding it to the total. This function takes the arguments ( range, criteria,
sum_range ). The range argument specifies the range you want to test, the criteria argument
specifies the conditional test to be performed on each cell in the range, and the sum_range
argument specifies the cells to be totaled. For example, if you have a worksheet with a
column of month names defined using the range name Months and an adjacent column of
numbers named Sales, use the formula =SUMIF(Months, "June", Sales) to return the value in
the Sales cell that is adjacent to the label June. Alternatively, you can use a conditional test
formula such as =SUMIF(Sales, ">=999", Sales) to return the total of all sales figures that are
more than \$999.
The SUMIFS function does similar work to that of the SUMIF function, except you can
specify up to 127 different ranges to sum, each with their own criteria. Note that in this
function, the sum_range argument is in the first position instead of the third position:
( sum_range, criteria_range1, criteria1, criteria_range2, criteria2, … ). The sum range and each
criteria range must all be the same size and shape. Using a similar example to the one
we used for the SUMIF function, suppose we also created defined names for cell ranges
Months, Totals, Product1, Product2, and so on. The formula =SUMIFS(Totals, Product3,
"<=124", Months, "June") returns the total sales for the month of June when sales of
Product3 were less than or equal to \$124.
Similarly, COUNTIF counts the cells that match specified criteria and takes the arguments
( range, criteria ). Using the same example, you can find the number of months in which total
sales fell to less than \$600 by using a conditional test, as in the formula =COUNTIF(Totals,
"<600").