Microsoft Office Tutorials and References

In Depth Information

**SUMIF**

The
SUM
function is one of the functions in Excel that can operate on a 3D range. The following

formula is an example of the use of a 3D-range argument.

=SUM(Sheet1:Sheet4!A1)

In this case, the values in the sheets from Sheet1 to Sheet4 inclusive are summed. However,

sheet names do not necessarily reflect sheet positions and only those sheets that are

“between” Sheet1 and Sheet4 will be included in the result.

Figure 10.25

Use the
SUM
function

to sum information in

lists or across

worksheets.

SUMIF

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

=SUMIF(range,criteria,sumrange)

It is important to note that the
RANGE
and
SUMRANGE
arguments do not have to reside on the

same worksheet (example shown later in this section).

=SUMIF(Sheet1!A1:A10,”>5”,Sheet2!A1:A10)

In this case, the values in the range
Sheet2!A1:A10
are summed if the corresponding values

in the range
Sheet1!A1:A10
are greater than 5.

An example of the utility of the
SUMIF
function is shown in Figure 10.26. The table represents

sales data from a variety of regions. The formulas shown in C17 and C20 summarize this

information according to region names that fit given criteria. The formula in B12 looks at the

descriptive text in A12 and uses it in the criteria argument to return the sum of the data in

C7:C14 that matches any word in B7:B14 that starts with “north”. So, the result is 73.9

because the
SUMIF
function sums the values in cells A2, A3, and A9. The formula in B13 works

in a similar manner, summing the cells in C7:C14 that matches any word in B7:B14 that ends

with “east”. In both cases, the wildcard character “*” is concatenated with the cell text to