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.
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.
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.
Source link
