This is the second episode in a series of indeterminate length.
Episode 1 looked at conditional formatting in PivotTables. This time we’ll look at the rather more prosaic task of adding up.
If you’re familiar with only one Excel function it is likely to be SUM(). Not only is adding up a block of cells one of the most frequent actions performed in Excel, but there is also a toolbar and ribbon tool to do it for you – the AutoSum tool. There is also a keyboard shortcut for AutoSum: Alt+=. Both tool and shortcut create a formula that uses the SUM() function. In the following Excel 2007 example, we have used Alt+= to create the totals in A4, A7 and A8. Excel is clever enough to restrict the ranges to stop at other SUM() function formulae and to construct A8 as the addition of the two previous sums:
If you are still using the Excel 2010 beta you may find it behaves differently. Here’s the result of the same sequence in Excel 2010 beta version:
This has been ‘corrected’ in the release version to work as it does in Excel 2007.
SUM() is not the only function that you can use to add up in Excel. If you use the Data, Subtotal option then the totals created use the SUBTOTAL() function rather than SUM(). SUBTOTAL() is more flexible than SUM(), it includes an additional argument that specifies the type of aggregate function to use: for example 1 for AVERAGE, 2 for COUNT, 9 for SUM. It is also possible to exclude hidden rows from the total by adding 100 to the argument, so 101 for AVERAGE, 101 for COUNT and 109 for SUM all excluding hidden rows. In this example we have hidden rows 19 and 20. Column B uses the argument ‘9’ to include hidden rows in the total as in cell B22:
=SUBTOTAL(9,B19:B21)
Column C uses ‘109' as in C22:
=SUBTOTAL(109,C19:C21)
This is not the end of the SUBTOTAL() function’s talents. Unlike SUM(), SUBTOTAL() will ignore other SUBTOTAL() functions in a range, so in B25, although the formula is:
=SUBTOTAL(9,B16:B23)
It doesn’t include the subtotals as well as the individual items.
Excel 2010 introduces an even more flexible function – AGGREGATE(). This includes additional options for the type of aggregate including percentiles and quartiles and also 8 exclusion options for dealing with other SUBTOTAL() and AGGREGATE() functions as well as hidden rows and error values.
As well as ‘simple’ total functions there are several functions and techniques for dealing with conditional sums including SUMIF(), the Excel 2007 SUMIFS() and the use of array formulae. An
earlier series of posts looked at some of the options.