SUM Is for Beginners—Excel Pros Use This Instead

You’ve probably used SUM a thousand times. Everyone has. SUM is fine for school assignments and tiny tables, but in the real world, it’s a blunt instrument.

If you want totals you can actually trust, there’s a better way—one that I wish I’d found years (and headaches) ago.

What’s Wrong With SUM?

SUM adds everything, visible or not. That’s its job. But what about when you filter your data or hide some rows? SUM happily includes all those hidden numbers. As a result, it inflates your total and quietly sabotages your reports. If you’ve ever found yourself explaining why your “total” is bigger than the filtered data, you know exactly what I’m talking about.

This is where most people hit a wall—I know I did. I relied on SUM, COUNT, and all the usual suspects, constantly tweaking my formulas whenever something didn’t add up. Then I discovered a function that handles everything those basics do, but without the usual headaches. That’s when I found SUBTOTAL.

How SUBTOTAL Actually Works

Imagine you’ve got a sheet full of sales data, hundreds or thousands of rows. Maybe you want to see only “Product A” sales, so you filter the column. The numbers vanish from view, but SUM doesn’t get the memo. It’s still adding up every row in the background, including the ones you can’t see. This goes for the COUNT and AVERAGE functions too.

SUBTOTAL, on the other hand, adjusts itself on the fly. With SUBTOTAL, when you filter your data, your total updates automatically to show only the visible, filtered rows.

=SUBTOTAL(function_num, range)

This isn’t just about sum totals either. SUBTOTAL can swap between sum, average, count, min, max, and a bunch of other useful calculations, just by changing that first number in the formula. Below is a full table of the supported functions:

You can tell SUBTOTAL to include the hidden rows by dropping the first digit from the function_num. E.g., 1 will SUM the hidden cells, but 101 will ignore them.

Unlike SUM, SUBTOTAL is smart enough not to double-count itself. If you have subtotals for each category and then a grand total at the bottom, SUBTOTAL knows to skip the other subtotals. SUM just piles everything together and bloats your numbers. If you’ve ever seen a total that’s way too high and wondered why, check for nested SUMs. SUBTOTAL doesn’t have that problem.

How to Use Subtotal in Excel (And Google Sheets)

What makes SUBTOTAL such an obvious choice is its flexibility—you get way more options without any extra complexity compared to SUM. Let’s take the same example and see how using SUBTOTAL can make things even easier.

To get the sum of the cells, the formula will be as below:

=SUBTOTAL (109, C2:C15) 

This formula sums the cells from C2 to C15, while ignoring the hidden cells. I’ll apply this to the two other rows, and now I have the totals. They work fine for the full table, and they work just fine when I filter the table. Now the numbers make sense.

The other numbers still don’t make sense, though. The COUNT is still showing 14, and my averages are now dividing the subtotal by that count, which is why the averages are lower than they should be. No worries. SUBTOTAL also supports the COUNT and COUNTA functions.

So, for my count cell (B16), instead of writing:

=COUNTA(A2:A15)

I’ll swap it for:

=SUBTOTAL(103, A2:A15)

103 is the function number for COUNTA. Now, my counts adjust automatically when I filter data, and my averages always show the right value. SUBTOTAL also supports MAX and MIN, which is a lifesaver.

In total, SUBTOTAL covers 11 different functions—so while it won’t replace every Excel formula, these 11 are really all you need for most summary tables.

SUBTOTAL works in Google Sheets, too. Everything you’ve learned here applies no matter which platform you use.

Look, there are only two reasons to stick with SUM. Either you genuinely never filter, never hide rows, never hand off your files to anyone, and never need to check your numbers, or you like explaining why your totals never match what’s on the screen.

For everyone else, there’s really no excuse. Switch to SUBTOTAL and your spreadsheets become dynamic, reliable, and basically error-proof for day-to-day reporting.