SUM problems on an AUtoFilter range

Hi,

If I use SUM($A$1:$A$200) I get the sum of those cells no problems as expected.

However when I autofilter and only about half are visible, the SUM goes out of sync and shows different value, what I need is it still to SUM all the cells in the defined range regardless of whether its visible/filtered or not.
MirageSFAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
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.
0
MirageSFAuthor Commented:
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.
0
Rob HensonFinance AnalystCommented:
How is the filter being applied, is it just using the AutoFilter dropdowns on the column header?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

MirageSFAuthor Commented:
Hi,

Yes, I have row 20 with about 10 headers, and then selected those and applied the AutoFilter
0
MirageSFAuthor Commented:
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.
0
Rob HensonFinance AnalystCommented:
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
0
MirageSFAuthor Commented:
That's exactly it
0
Rob HensonFinance AnalystCommented:
Don't fully understand your last comment.

So your data also has Subtotal groupings??? Is that what you're saying?
0
MirageSFAuthor Commented:
Must be a bug as just uploaded work sheet to personal computer and opened and SUM works exactly as it should!.
0
MirageSFAuthor Commented:
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.
0
Rob HensonFinance AnalystCommented:
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.
0
MirageSFAuthor Commented:
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
0
Rob HensonFinance AnalystCommented:
Might be worth referring to MS Tech Community:

https://techcommunity.microsoft.com/t5/Excel/ct-p/Excel_Cat
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MirageSFAuthor Commented:
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
0
Rob HensonFinance AnalystCommented:
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.
0
MirageSFAuthor Commented:
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
0
Rob HensonFinance AnalystCommented:
To be of any further help I think we are going to need to see the workbook.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.