• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 43
  • Last Modified:

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.
0
MirageSF
Asked:
MirageSF
  • 9
  • 8
1 Solution
 
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now