SUM problems on an AUtoFilter range

SUM does not normally adjust for hidden/filtered rows. There are formulas available which do adjust though, eg SUBTOTAL.

Can you upload the file showing the problem.

that's exactly what I thought, however SUM is definitely going weird.  Would take me too long to strip it down to post the book.

How is the filter being applied, is it just using the AutoFilter dropdowns on the column header?

Hi,

Yes, I have row 20 with about 10 headers, and then selected those and applied the AutoFilter

Its weird, when I use the same method on my personal computer using Office 2016 it works as expected, on my works machine running 2010!  It plays up.

I have found an instance where SUM does adjust.

See attached file with some random data.

I have grouped and subtotalled the data by Code in column A and then cell B56 has a SUM of the cells above.

When all rows are visible the SUM gives 151023 but when the filter is applied to column A for only one group (Q for example) it adjusts to 59565 which isn't even the sum of those visible, that should only be 13836.

Is this similar to your scenario?
SUM-issue.xlsx

That's exactly it

Don't fully understand your last comment.

So your data also has Subtotal groupings??? Is that what you're saying?

Must be a bug as just uploaded work sheet to personal computer and opened and SUM works exactly as it should!.

No the SUM value gives the correct figure, then when you filter it gives a value that does not even corresepond with whats on display, when it should still have the same value as previous.

I am using Excel 2013 and it doesn't work as expected, maybe it is something that MS fixed with Office 2016.

I have worked out how the SUM shows 59565 in the sample I uploaded but cannot explain why.

In the sample, the true total of all items (subtotalled) is 50341, the total of items in Q is 4612. The 59565 is the overall total plus the items showing plus the total of those items ie Q doubled up.

I also wrote a function 2 methods to calculate the range, and both of them are effected by filtering when they shouldn't be.  Damn, without that got to reference a different sheet to get my required results

The PLOT thickens,  if I opt out/in on the filter for any of the values for example unlick 0 to view all values except those with 0 then it works fine, as soon as I as the filters above that i.e. sort by Highest first etc, then the sum goes mad

Removing 0 from any filter is not going to have any impact on the sum as it is just removing cells that didn't provide anything to the total in the first place.

Was just an example, I can remove anything and the SUM remains the overall TOTAL regardless, but soon as I select either of the SORT options it breaks

To be of any further help I think we are going to need to see the workbook.