Why is SUBTOTAL in Excel better than SUM?

SUM is the best-known formula in Excel.

It is versatile, easy to use and even has its own icon on the ribbon. However, there are times when SUM can lead you astray. Take for example where you need SUMs of other SUM formulas. This can happen in any sort of accounting with subtotals.  You may have (for example) income and expenditure items which you need to subtotal, and then later you need to total the subtotals of the income and expenditure.  Other examples are Bills of Materials and Schedules of Quantities.

At the lowest level, you can easily total the individual items of income and expenditure separately. But then to total those totals, you need to set up your own formula because if you used a SUM, you would also be including the previous SUMs in that SUM.

This is where the SUBTOTAL function comes into its own. A SUBTOTAL can include other SUBTOTAL formulas in its range and those formulas are ignored in the final SUBTOTAL. An example might make this clearer:

Example:

Consider a fictitious electronic device for which the component list reads as follows:

The device has three assemblies, numbered 100 to 300 in the first column.  It has 3 levels of assembly, with the level number in the third column (“Level”).

The Total Quantity of each component times the Unit Cost give us the Extended Cost.  Using a SUM, we total that for the products, along with the Total Quantity of components.

All that is fine.  Until someone asks for costs for each subassembly, to see where the money is going or might be saved.

We might then have this situation (Extended Cost formulas documented on the right):

As we can see, putting another level of SUM inside the existing Total for Product spectacularly corrupts the latter.  The Assembly Totals are totalled into the final SUM.

We show a common fix for this on the bottom line of the table.  There, we have abandoned the SUM.  Instead, we add in the Assembly Totals individually.  This can work on a small scale.  But it is tedious and prone to error, particularly in larger examples.

Is there a better way?

Of course!

Let us go back to the original example.  Remove the row of SUMs at the bottom.  Click somewhere in the table.  On the Data ribbon, in the Outline group, click Subtotal:

Select Total Quantity in addition to Extended Cost, which is selected automatically.  Click OK.

The result looks like this:

Excel has inserted rows for the Assembly Totals, which show the totals we expect from the previous example.  Then it has a Grand Total at the bottom, which shows the correct figures, matching the original SUMs.

How was this achieved?

As we can see in the figure, Excel did not use a SUM.  Instead, the Grand Total is calculated as =SUBTOTAL(9, G2:G16). The Assembly Totals likewise use =SUBTOTAL(9, Range) for the appropriate ranges.

A SUBTOTAL, you see, will not subtotal other SUBTOTALs within its range.

SUBTOTAL has its quirks. One of them is visible in the example above. Where we would just have used SUM, we use “SUBTOTAL(9”. Why “9”? Unlike SUM, which is a single-function function if you like, SUBTOTAL is a portmanteau function, a multi-function function. SUBTOTAL embodies not only SUM, but also Average, Max, Min, and a number of other statistical functions. SUBTOTAL(1, for example, is not a sum but an average. We will look at the full list shortly.

The syntax of SUBTOTAL is:

SUBTOTAL(Fn_num, Range)

The SUBTOTAL function takes the following arguments:

Fn_num: Required. The number 1-11 or 101-111 specifies the function to use for the subtotal. 1-11 includes manually-hidden rows, while 101-111 excludes them; filtered-out cells are always excluded.

Fn_num (includes hidden rows)	Fn_num (ignores hidden rows)	Function
1	101	AVERAGE
2	102	COUNT
3	103	COUNTA
4	104	MAX
5	105	MIN
6	106	PRODUCT
7	107	STDEV
8	108	STDEVP
9	109	SUM
10	110	VAR
11	111	VARP

Note: the SUBTOTAL function automatically ignores other SUBTOTAL formulas that exist in the range, to prevent double-counting.

Reminder if you skipped ahead: Excel has a Subtotal feature that automatically inserts SUBTOTAL formulas in sorted data. This feature is located at Data > Outline > Subtotal. SUBTOTAL formulas inserted in this way use the standard function numbers 1-11.

So, why is SUBTOTAL better than SUM?

Because (a) it does not include other subtotals within its range, thus producing correct answers to any level of nesting, and (b) it does not just do a SUM, but also AVERAGE, COUNT, COUNTA, MAX, and MIN, etc.

But, if you only have one level, please carry on using SUM, AVERAGE, COUNT, COUNTA, MAX, MIN, and the rest!