Advanced Techniques for Summing Values

Using the SUM function to total a column of values is an essential skill in Excel. However, it is not always that straightforward. The column of values may contain errors, or the values you want to sum may be on many different sheets.

This article looks at 4 advanced techniques for summing values including;

1. Summing a range that contains errors
2. Creating a cumulative total
3. A consolidated sum from multiple sheets
4. Summing Every Nth Value in a Range

Sum a Range that Contains Errors

If the range of values you wish to sum contain an error, your SUM function will also return an error.

The AGGREGATE function released in Excel 2010 enables you to ignore error values when summing.

This function is very versatile and can also be used to calculate other aggregates such as the count, average or max values in a range. As well as ignoring errors, it can also be used to ignore hidden rows and subtotals in a range.

The image below shows the AGGREGATE function being used to sum a range of cells containing error values. In the formula, the number 9 specifies the use of Sum, and the number 6 specifies to ignore error values only.

View the video below for a complete explanation of the AGGREGATE function.

Create a Cumulative Total

You may want to create a cumulative total, or running total, on your spreadsheet to add the values onto the previous value.

This cumulative total can be created with a simple formula as shown below.

This image shows a running total for the months of the year. The formula starts in cell C3 and has been exposed in the image to show its inner makings. This formula has been copied to cells C4:C13.

Sum a Value from a Cell on Multiple Sheets

You may have values on multiple sheets that need summing. For example, these sheets may represent different months of the year, different regions or maybe different promotions that are running.

Let’s imagine the cell containing the value is in the same position on each sheet. For example, in cell B10. And the sheets you are using are grouped together and not separated by any other sheets.

You can write a SUM function to consolidate all of these values by;

1. Begin typing the formula =SUM(
2. Click on the first sheet of the range of sheets you want to use.
3. Hold the Shift key on the keyboard and click on the last sheet in the range.
4. Click on the cell containing the value one each sheet.

The end result will be something like below. In this example, January is the first sheet and December in the last sheet in the range.

=SUM(January:December!B4)

You could also just type this formula rather than follow the steps listed above.

Sum the Value from Every Nth Cell

It could be that the values you wish to sum are not in consecutive cells. Take the image below for instance where the values to be added are in every 5^th cell.

For this complex task we need the SUMPRODUCT function. This powerful function can handle complex criteria when adding or counting values (learn more about the SUMPRODUCT function).

The formula below is used to sum the values from every 5^th cell as in the example above.

=SUMPRODUCT((MOD(ROW($B$3:$B$22)-2,5)=0)*($B$3:$B$22))

The ROW function is used to return the row number of the current value. 2 is subtracted from this row number because in this example the list starts from row 3, so the 5^th row is row 7. So the value to add is always 2 cells above the multiples of 5 (5, 10, 15, 20).

The MOD function is used to divide the row number by 5 to determine if it is the 5^th cell. MOD will result in 0 if it is the 5^th row so SUMPRODUCT tests if it is equal to 0.

SUMPRODUCT then multiplies the result (which will be 1 if true) with the list of values. This is then totalled.
This is a complex formula so for a more complete understanding watch the video below.