Excel Sum Function

%3CLINGO-SUB%20id%3D%22lingo-sub-1963464%22%20slang%3D%22en-US%22%3EExcel%20Sum%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1963464%22%20slang%3D%22en-US%22%3E%3CP%3EEverytime%20I%20hit%20auto%20sum%2C%20my%20formula%20defaults%20to%20subtotal%20instead%20of%20sum%3F%26nbsp%3B%20This%20is%20frustrating%2C%20when%20I%20hit%20auto%20sum%20function%20it%20should%20sum.%26nbsp%3B%20Can%20it%20to%20sum%20at%20all%3F%26nbsp%3B%20Only%20subtotal.%26nbsp%3B%20How%20can%20I%20fix%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1963464%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1963551%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Sum%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1963551%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F893674%22%20target%3D%22_blank%22%3E%40ehoward73401%3C%2FA%3E%26nbsp%3BAre%20you%20adding%20Autosum%20to%20a%20structured%20table%3F%20If%20so%2C%20Excel%20defaults%20to%20%3DSUBTOTAL(109%2C......).%3C%2FP%3E%3CP%3EIf%20you%20Autosum%20a%20regular%20range%20it%20should%20give%20you%20%3DSUM(........)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1963730%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Sum%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1963730%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F893674%22%20target%3D%22_blank%22%3E%40ehoward73401%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExcel%20by%20default%20use%20SUBTOTAL(9%2C..%20if%20the%20range%20is%20filtered.%20That's%20to%20exclude%20filtered%20cells%20from%20summing.%20If%20clean%20the%20filter%20it%20shall%20be%20SUM()%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Everytime I hit auto sum, my formula defaults to subtotal instead of sum?  This is frustrating, when I hit auto sum function it should sum.  Can it to sum at all?  Only subtotal.  How can I fix?

5 Replies

@ehoward73401 Are you adding Autosum to a structured table? If so, "my" Excel defaults to =SUBTOTAL(109,......), which is basically the same as SUM, though it excludes hidden (filtered out) rows

If you Autosum a regular range it should give you =SUM(........).

@ehoward73401 

Excel by default use SUBTOTAL(9,.. if the range is filtered. That's to exclude filtered cells from summing. If clean the filter it shall be SUM()

@Riny_van_Eekelen 

Sorry, missed your answer, had some break between starting an answer and POST

@Sergei Baklan No problem! In fact, your answer reveals a difference between Excel's behaviour on Windows and Mac. On a Mac (i.e. my Mac, MS365, Insider) a clean filter defaults to SUBTOTAL(109,.....)

@Riny_van_Eekelen 

That's different behavior for tables and ranges. For ranges SUM() returns back if clean the filter.