Saturday, March 27, 2021
SUBTOTAL vs SUM Excel Formula - Excel "SUBTOTAL" formula is one of the exciting excel formulas that you may come across.
The first thing you should know, is that SUBTOTAL doesn’t just add up numbers like SUM, it can do eleven different calculations.
Syntax
SUBTOTAL(function_num,ref1,[ref2],...])
=SUBTOTAL(9,A1:A2) is same as =SUM(A1:A2), however…
SUBTOTALs in SUBTOTAL range are ignored
One of the main issues that excel users face while working with large financial data, is double counting. This occurs when they have to deal with a lot of SUM formulas in a row. SUBTOTAL resolves the issues with ease by ignoring any cells that have subtotals.
Hidden Rows Can Be Ignored
One possible limitation of SUM, is that it adds up all of the values within a range, even if you were to hide one or more rows. However, SUBTOTAL gives you the option to add hidden rows (same as SUM formula aka function number 9) or ignore hidden rows (function number 109).
SUM of Filtered Table
If you use SUM to add values in a filtered table, it will add all of the values within the table instead of the filtered values.
Again, SUBTOTAL excel formula simplifies things by adding only what has been filtered. It is compatible with both the 9 and 109 function numbers.
Automatic SUBTOTALs Feature
Under the excel "Data" menu, there is a Subtotal button. It is a feature that automatically inserts subtotals within related data. Highlight your table and click on the Subtotal button. You can then specify which group needs subtotalled.
Even superheroes have their limitations. In this case the 109 function is not capable of processing horizontal data. For instance, =SUBTOTAL(109,A1:F1) will add all values in a range, even if any column is hidden.