• Go Ask Debbie


Updated: Jun 24, 2021

Do you know the difference between =SUM and =SUBTOTAL in Excel?

Both provide totals, but when cells are hidden, the =SUM will provide the total for the entire column of data. But, what if you only want the total for the cells that are showing and don’t want to include those that are hidden?

The answer is to use the =SUBTOTAL function in Excel.

The syntax requires a reference as the first parameter – 109 tells Excel to use the SUM function and to NOT include hidden cells.

See the example below.

Using =SUM shows the entire total of $20,281.75, but =SUBTOTAL shows the total of ONLY those cells that are showing (hidden cells are hidden even when calculating the total): $11,395.77.

If you prefer video, I created this YouTube #Shorts; watch here:

Try this the next time you want to get a total of only the data that is showing.


If you're interested in an entire course on Excel Pivot Tables, Analyzing Data, Macros, and move, CLICK HERE for a 50% discount.


Recent Posts

See All