There’s SUM and then there are all the useful SUM variations to choose from.
Excel’s SUM() function is probably the first one we learn but there’s a lot more to it than simply clicking on the button to add up a list of numbers. In this article we’ll look at the options available in SUM and related functions.
SUM
SUM is pretty simple. SUM(B2:B5) will add up the numbers in cells B2, B3, B4 and B5.
Use a comma to separate cells you want to add up eg SUM(B2,B23,CD30) adds up the single cells B2, B3 and CD30 (in a large worksheet). Up to 255 individual references are possible.
You can mix ranges and single cells eg SUM(B2:B5,CD30).
SUM can add up references to cells, range, named range, formula or array.
Allowing formulas gives you flexibility to SUM without needing an intermediate cell to calculate before adding up. =SUM(B3 * PI(),B2) takes the value in B3 multiplies by PI (3.14159…) then adds the value in B2.
But what if one of the cells has text instead of a number? A cell with text, logical or empty are ignored by SUM. An error cell (#N/A) will cause an error in SUM.
SUMPRODUCT
This is a handy function shortcut for a common need – to multiply sets of numbers then add up the results.
Here’s a basic example. =SUMPRODUCT(B2:B6,C2:C6) multiplies each value in the first range (Column B) with the matching value in the second range (Column C) and adds up the results.
As you can see, the SUMPRODUCT result is the same as the total of all the individual stock values in Column D.
Two ranges or arrays is usually what SUMPRODUCT does but it can be expanded to more values.SUMPRODUCT(G2:G6,H2:H6,I2:I6) will multiply three values (eg G2*H2*I2) then add up the results.
Naturally the size of each range/array has to be the same, e.g. SUMPRODUCT(B2:B6,C2:C3) will give you a #VALUE! error because there’s 5 cells in the first range but only 2 in the second.
SUMIF
Quite often you have a list of values but don’t want to add them all up. SUMIF and SUMIFS let you add up values according to a filter you set without changing the source information.
SUMIF
Let’s you add up numbers that match a filter you set as the second criteria. Optionally you can set a third parameter as the list to add up:
SUMIF(range, criteria, [sum_range])
Using the table above:
=SUMIF(D2:D6,”>6000″) will add up only the values that are greater than 1,000. In the above example, only the Tyrell Corp and SPECTRE have values of stock holdings higher than $6,000 so only those two are added up.
More commonly you want to add up values using another criteria. For example, add up the stock values for stocks with higher individual prices. =SUMIF(C2:C6,”>6.00″, D2:D6)
SUMIFS
SUMIFS lets you apply multiple criteria before adding up the filtered results.
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Note: the order of parameters is different from SUMIF – in SUMIFS the range to be added up comes first, then pairs of criteria range and filter.
=SUMIFS(D2:D6, C2:C6,”>6.00″ ) is the same as the last example above, with the same parameters in a different order.
=SUMIFS(D2:D6, C2:C6,”>6.00″, B2:B6,”>5000″) will only add up values with a stock price above $6.00 and a stock holding of more than 5,000 shares.
Tip: If you use SUMIF and SUMIFS regularly it’s easy to get the parameters confused. You’ll find it easier and safer to use just SUMIFS and have a single consistent order to remember for parameters
SumIFS() got some improvements with Excel 365 Faster SumIFS coming to Excel 365 for Windows
SUMIFS Excel SUM with filters galore
Great and simple ‘live’ sorting with filtering in Excel
Excel Subscriptions Manager
Filters are strings
In both SUMIF and SUMIFS the filters are text strings. That means you can point the filters to cells and let users enter their own limits into those cells.
In this example, you can type in a filter at B8 to change the filtered SUM in B9 without changing the formula each time.
DSUM
DSUM() is a lesser used but much expanded version of SUMIF which can be used to apply multiple filters from a database-like table and add them up.
SUMSQ
SUMSQ()squares each result before adding up.
SUMSQ(4) = 16
SUMSQ(5,3) = 25 + 9 = 34
There are more complex variants on SUMSQ, each takes two parameters as cells or ranges:
SUMX2MY2() calculates the difference between two squares and then adds up the results
SUMX2PY2() multiplies the squares of two given values and then adds up the results
SUMXMY2() gives you the sum of squares of differences of corresponding two values.
Also AVERAGE and COUNT
The AVERAGE and COUNT functions have similar related functions to SUM.
AVERAGE will return the average/mean of the values. It takes the same arguments as SUM.
COUNT returns the number of numeric cells in the range given. What Excel defines as ‘numeric’ probably isn’t what you expect and is a topic for another time.
There are ‘A’ variants AVERAGEA and COUNTA which include text and logical values in cells (they are ignored by AVERAGE and COUNT)
There are ‘IF’ variations AVERAGEIF and COUNTIF plus AVERAGEIFS and COUNTIFS
Note: AVERAGEIF and AVERAGEIFS have the same problem as SUMIF/SUMIFS in that the parameter order changes. Best to stick with AVERAGEIFS and save yourself confusion.
See Also
- Average / Mean options in Excel
- Excel – Nested IF’s and alternatives
- Excel’s IF function
- Excel SUM anomaly
- Beyond the simple =SUM function in Excel
- AutoFill in Excel 2003 – Part 1
- COUNTIF for Excel
