Sumifs and Table Filtering

Hi,

This is one that's puzzled me for quite some time.
I use a lot of tables (usually from SQL) and I generally report figures from tables using SUMIFS.

Most of the time it works fine but, seemingly randomly, I run into problems with filtering.

The problem is that I want my SUMIFS functions to always show summarised numbers from a table no matter what the filter state is. I'm finding that sometimes when I filter a table, all SUMIFS that fall outside of the filters criteria show zero - I would like them to continue to show the results as if the table wasn't filtered.

I only seem to run into this problem now and again but when I do, it completely puzzles me - it seems that the default behaviour is for SUMIFS to show results no matter what the state of the filters are and, searching on the web I can see that most questions are going the other way - ie 'how do I get SUMIFS to recognise filters' - I'm completely confused.

Example - I have a table coming in from SQL containing the TB for a large company. I have around 50 tabs that show P&L and Balance Sheet data for various divisions and departments of the business. If I filter the table, all of the sheets continue to show complete data (via SUMIFS).
If I create a new spreadsheet and pull the same data in from SQL and create a sheet with several SUMIFS looking up data with different criteria and I then filter the table to show only one category - all SUMIFS that have criteria for other categories revert to zero.

I'm not sure why i'm getting inconsistent results? ...is there a setting that I've missed?

Thanks Ali,

I think it's slightly different - I want SUMIFS to work no matter what the table filters are doing (visible and non-visable cells) - which I think is the default behaviour.

I'm getting inconsistent behaviour ...most of the time SUMIFS ignore the filter (which I like) but sometimes SUMIFS will only work on visible cells.

I'm wondering if there's some kind of setting that I need to apply? ...or perhaps there's been a change in Excel. Most of my spreadsheets have evolved over years and therefore originated in previous versions of Excel - I seem to get the above problem a lot when I create a new spreadsheet (I'm using 365 MSO (16.0.13929.20206)).

Originally Posted by aligw

difficult to say ...

Are you still using excel 2017?

ms 365 mso (16.0.13929.20206)

Ah - I've fixed my own problem!

I'd added a custom column to the SQL table which summed the months of the financial year using autosum - I didn't check but autosum automatically created a SUBTOTAL function (=SUBTOTAL(9,DB_Con_TBQ_2022[[Oct-21]:[Mar-22]])) and therefore hidden rows would be zero!

I've change the formula to =SUM(DB_Con_TBQ_2022[[Oct-21]:[Mar-22]]) and now everything works!!

...this has been bugging me for a couple of years - I guess coming on here has forced me to think about it in enough detail to solve the problem!