How many ways can Excel add thee, let me count the ways. Butchered Elizabeth Barrett Browning quotes aside, Excel has many functions for summing numbers. Last time I counted the different SUMs in Excel 2013, there were eight different SUM functions. There are SUM, SUMIF, SUMIFS, SUMPRODUCT, SUMSQ, SUMX2MY2, SUMX2PY2, SUMXMY2. I had never even heard of the last four functions until I started to write this blog.
I would say, though, that the most common ones are SUM, SUMIF and SUMIFS.
How do you know which one of these three functions to use?
Let’s take a look at each function and see what they do. That should help us decide when to use each function.
Use the Lowly SUM
SUM(number1, number2, …)
- number1 = first number, cell, or cell range to sum
- number2 = second number, cell, or cell range to sum
- … = next number(s) to sum, up to a total of 255
SUM is the most basic of the SUM functions. It adds the numbers entered between the brackets. This can include hard-entered numbers, cells, cell ranges, or a mix of them. Text values are ignored.
The formula =SUM(E1:E5) in the image below will sum the values in cells E1:E5, which for this example results in 19.
Expand Your Spreadsheets With SUMIF
SUMIF(range, criteria, [sum_range])
- range = the cells that you want Excel to look in for a match to your criteria. It can be the same range as the range of cells that you are summing, or a different range of cells
- criteria = what you want to find in the range. Wildcard characters can be include
- [sum_range ] = If you want to sum a range of cells different from the first range, you enter the sum range here. This is optional
Like SUM, SUMIF also adds the numbers that are entered between the brackets, however you can specify one criteria that has to be matched in the range for it to included in the SUM.
For example, if you have a column with color names and a column of values, you can specify what matching color to sum. The formula =SUMIF(D1:D5,”Blue”,E1:E5) in the image below will sum the values in the second column (E1:E5) that have a matching “Blue” text in the first column (D1:D5), which for this example results in 7 (2+5).
Supercharge Your Formulas With SUMIFS
SUMIFS(sum_range, criteria_range1, criteria1, …)
- sum_range = the cells to sum
- criteria_range1 = the cells that you want Excel to look in for a match to your criteria
- criteria1 = what you want to find in the range
- … = additional range and criteria, up to 127
Like SUMIF, SUMIFS sums a range. However, it allows multiple criteria to be specified.
Notice the difference between SUMIF and SUMIFS in the order of the arguments.
Expanding on the example above, let’s say you have a column with color names, column of values, and another column of items. You can specify what matching color and item to sum.
In this example, we will sum the value of all of the pencils that are blue.
The formula =SUMIFS(E1:E5,D1:D5,”Blue”, F1:F5,”Pencil”) in the image below will sum the values in the second column (E1:E5) that have a matching “Blue” text in the first column (D1:D5), and a matching “Pencil” in the third column (F1:F5), which for this example results in 5, since there is only one row that has both “Blue” and “Pencil”.
One beneficial and practical use of both SUMIF and SUMIFS is the ability to create a grand total or summary of a large amounts of cells.
Following the examples above, say you have hundreds of rows of data of colors and want a summary of the total value for each color. SUMIF and SUMIFS are excellent functions to simply create that summary.
Hopefully, now you have found that SUM, SUMIF and SUMIFS are some really great functions in Excel.
Perhaps you have thought of some creative and interesting ways to use these functions. If so, please comment on how you have used them.