Excel Sum Function

[ehoward73401]

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?

[#2]

@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(........).

[#3]

@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()

[#4]

@Riny_van_Eekelen 

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

[#5]

@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,.....)

[#6]

@Riny_van_Eekelen 

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