Dec 07 2020 08:59 AM
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?
Dec 07 2020 09:16 AM - edited Dec 07 2020 09:22 AM
@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(........).
Dec 07 2020 09:41 AM
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()
Dec 07 2020 09:43 AM
Sorry, missed your answer, had some break between starting an answer and POST
Dec 07 2020 09:57 AM
@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,.....)
Dec 07 2020 10:34 AM
That's different behavior for tables and ranges. For ranges SUM() returns back if clean the filter.