Today’s Formula Friday will be looking at the Sum Function vs the Subtotal Function.
If you want to download the Formuala Friday examples you can so so here
[wpdm_package id=’10703′]
Even though SUM is probably one of the most straightforward and most used formula in Excel, it sometimes does not serve so well in certain situations.
Ever tried to use SUM in these scenarios?
- Try to calculate Sum Total when you have hidden or filtered rows
- Try to calculate Grand Total when you have Sub Totals in your data column.
How did it work out for you?. Not very well I would suspect. Yes, surprisingly SUM is not so good in the above situations, but SUBTOTAL can come to the rescue.
First let’s take a look at how many different functions SUBTOTAL actually offers.
The syntax of SUBTOTAL is
SUBTOTAL(function_num,ref1,[ref2],…)
Where
function_num – this is a required field and the number 1-11 or 101-111 that specifies the function to use for the subtotal. 1-11 includes manually-hidden rows, while 101-111 excludes them, but filtered-out cells are always excluded in the calculations.
ref1 Required. The first named range or reference for which you want the subtotal.
ref2,… Optional. Named ranges or references 2 to 254 for which you want the subtotal.
This is how the function_num is seen when typing the formulas.
We will easily see the difference between SUM and SUBTOTAL when we perform all three functions on the same data set and get very different results. See my data set up below and address the first issue on our list above.
1. Try to calculate Sum Total when you have hidden or filtered rows
I have used the 2x SUBTOTAL function and SUM functions on the same range of cells D4:D80. Initially this gives the same result of 543758.
So let’s filter our data, and select all sales of all categories for Jan 2016. See that both SUBTOTAL formulas adjust themselves to accommodate the data we have filtered out, but SUM is not as dynamic and still gives us the total sales for the whole data set.
Now let’s take this a step further and hide some rows of data, and see what happens. We can hide a few of the rows of cells by Format – Hide/Unhide or by Grouping. In this example I have hidden all data associated with the Shoes Category. See the results below.
SUM has given us as expected the full data set sales for all categories and all dates. SUBTOTAL(9 has given the full data also as this number 9 function does not recognise hidden cells, but SUBTOTAL(109, does take into account hidden rows as easily gives us a correctly filtered data set result. You can see that the use of SUBTOTAL can gives s a relatively dynamic working of our data set.
2. Try to calculate Grand Total when you have Sub Totals in your data column.
Let’s move onto our next scenario where SUM function again really does not give us the flexibility we need. I have added Subtotals to my data set. These can be added manually or by Data – Subtotal, either way we have some Subtotals now added to our data set for each category of sales.
I have used the automatic method and which has resulted in a data set with subtotal intersected at a change in every sales category. You can also see immediately that once again SUM has let us down!! as it it has taken every sales value and subtotal in the data set, wheres both flavours of the SUBTOTAL formulas have ignored the SUBTOTAL sales values and ignored the subtotal values and given is the overall sales values only.
Do you use SUBTOTAL?. did you use if before you used Pivot Tables?. Share in the comments below.
Dont forget to sign up to the Excel at Excel Newletter for 3 free Excel tips the first Wednesday of the month. Just click on the Sign Up Form to the right or use the link below.
