|
Without any doubt, SUM is the preeminent function in the Math & Trig category of Excel functions. This function is so central to spreadsheet formula-making that the Standard toolbar contains its own AutoSum button to facilitate the construction of all your SUM formulas.
The SUM function isn't the only function in the Math & Trig category capable of computing totals. Rather, the program offers more specialized summing functions for use when you need to total the products or squares of the values in a range or total values only when a particular condition is met.
Summing products, squares, and their differences
You can use the SUMPRODUCT function to have Excel total the products returned by multiplying the values in corresponding arrays. For one array to correspond to another, each must consist of the same number of rows and columns.
For example, suppose you have a spreadsheet with a 2 x 1 array in the cell range B2:B3 that contains the values 4 and 5 (expressed as {4;5}), and another 2 x 1 array in the cell range D2:D3 that values 6 and 3 (expressed {6;3}). Because both these arrays have the same number of rows (2) and columns (1), they correspond and can be used as arguments in the SUMPRODUCT function.
The SUMPRODUCT function uses the following syntax:
SUMPRODUCT(array1,array2, . . .)
Note that the SUMPRODUCT function accepts up to a maximum of 30 array arguments. The SUMPRODUCT function isn't the only summing function to use these arguments. The following summing functions also follow the same syntax as SUMPRODUCT:
- SUMX2MY2 (SUM X squared minus Y squared) to sum the difference between the squares of two corresponding arrays
- SUMX2PY2 (SUM X squared plus Y squared) to return the grand total of the sums of the squares in two corresponding arrays
- SUMXMY2 (SUM X minus Y squared) to sum the squares of the differences in two corresponding arrays
The SUMSQ function that totals the squares of the arguments is similar to these summing functions, except that you can use individual numbers as well as arrays for its arguments.
Conditional totals
All the variations of the SUM function you've used up to now calculate their totals come rain or shine. The SUMIF function, however, is a little different: It only sums its designated values when a particular condition is true. The SUMIF function, although located in the Math & Trig category, could have just as easily been classified as one of the Logical functions because it basically works only when its comparative condition returns the logical value TRUE.
The syntax of the SUMIF function includes the following arguments:
SUMIF(range,criteria,[sum_range])
The range argument specifies the cells that you want Excel to evaluate using the condition or conditions specified by the criteria argument. The optional sum_range argument specifies the cells you want Excel to sum when the condition in the criteria argument is found TRUE. You only need to specify a sum_range argument when the cell range to be summed is not the same as the one whose values are evaluated as to whether they meet the condition set up in the criteria argument.
 | The SUMIF function works great when you only have only one criterion that you want to apply in doing the summing. It does not, however, work when you have multiple criteria that you need to use in determining which numbers get added to the total and which don't. For those situations, you have to turn to the Conditional Sum Wizard, a nifty little Excel add-in tool that walks you through the steps of building more complex SUM formulas that utilize IF conditions that can include multiple criteria. |
| 
