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").

For more information about conditional tests, see “Creating conditional tests” in Chapter 12,

“Building formulas.” For more about using range names, see “Naming cells and cell ranges” in

Chapter 12.

Using selected mathematical functions

Excel has over 60 built-in math and trigonometry functions; the following sections brush

only the surface, covering a few of the more useful or misunderstood functions. You can

access them directly by clicking the Math & Trig button on the Formulas tab on the ribbon.