Microsoft Office Tutorials and References
In Depth Information
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.
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.
Use the SUM function
to sum information in
lists or across
The SUMIF function adds the cells specified by a given criteria.
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).
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