{"id":6160,"date":"2025-06-25T19:32:16","date_gmt":"2025-06-25T11:32:16","guid":{"rendered":"https:\/\/www.munmon.com\/v3\/?p=6160"},"modified":"2025-06-25T19:32:16","modified_gmt":"2025-06-25T11:32:16","slug":"filtered-data-dont-just-sum-it-up-subtotal-it","status":"publish","type":"post","link":"https:\/\/www.munmon.com\/v3\/2025\/06\/25\/filtered-data-dont-just-sum-it-up-subtotal-it\/","title":{"rendered":"Filtered Data? Don\u2019t Just SUM It Up. SUBTOTAL It"},"content":{"rendered":"\n

Good day, folks!<\/p>\n\n\n\n

Today I learned something about Excel \u2014 and yes, maybe some of you dah lama tahu… but sharing is caring, and learning never ends (especially when you\u2019re a librarian, dad, gamer, and accidental spreadsheet warrior).<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

So here\u2019s the situation:<\/p>\n\n\n\n

You use =SUM()<\/code> to total up your data.
Then you apply a filter.
But… the total doesn’t change.
Why? Because SUM()<\/code> includes everything<\/strong> \u2014 even rows you hid from your guilt<\/em>.<\/p>\n\n\n\n

\"\"<\/a>
At first glance, both SUM and SUBTOTAL show the same result \u2014 the total from the ‘Reporting_Period_Total’ row.<\/figcaption><\/figure>\n\n\n\n

Enter: Copilot<\/strong>. (See: This is how AI improves your puny human existence and makes you a more worthwhile version of yourself.)<\/p>\n\n\n\n

Copilot said:<\/p>\n\n\n\n

\n

\u201cEncik… if you want your totals to reflect only visible, filtered data \u2014 use SUBTOTAL()<\/code>.\u201d<\/p>\n<\/blockquote>\n\n\n\n

Boom. Enlightenment. SUBTOTAL is basically SUM\u2019s smarter sibling<\/strong>. It knows when to recalculate based on what you actually want to see<\/em> \u2014 the visible rows.<\/p>\n\n\n\n

\"\"<\/a>
Apply a filter, and you\u2019ll see the difference \u2014 SUM counts everything, even the hidden stuff. SUBTOTAL? It plays fair and totals only what\u2019s visible.<\/figcaption><\/figure>\n\n\n\n

Example:<\/strong>
Let\u2019s say you\u2019re tracking sales in column B (B2 to B100):<\/p>\n\n\n\n

=SUBTOTAL(109, B2:B100)<\/code><\/code><\/pre>\n\n\n\n
And yes… what is this mystical 109?<\/strong>
It\u2019s a code \u2014 an operation indicator. In this case, 109 = SUM only visible rows<\/strong>.
It’s like function within a function<\/strong> \u2014 Excel\u2019s version of Inception<\/em>.<\/p>\n\n\n\n
Here are more codes in the SUBTOTAL family:<\/p>\n\n\n\n