SUBTOTAL <> SUM [No filters]

[Andy Pilkington]

Hi, please can anyone explain this anomaly? I have a column of figures that are unfiltered where the result of the SUBTOTAL(9,??:??) function is ZERO when the SUM(??:??) function gives me a number. I happen to know that the SUM function is giving the correct answer.
Any ideas?
Thanks in anticipation.
Andy P

 

[Peter_SSs]

Does this happen for a number of ranges in various worksheets or just one single range in one worksheet?

Perhaps you could give us a sample of data with XL2BB where this does happen so that we can take a look?

When this does happen, is the number produced by the SUM function a very small decimal number?

 

[Andy Pilkington]

Does this happen for a number of ranges in various worksheets or just one single range in one worksheet?

Perhaps you could give us a sample of data with XL2BB where this does happen so that we can take a look?

When this does happen, is the number produced by the SUM function a very small decimal number?

The actual formulas are - =SUM(O10:O4236) which delivers a result of 3448580.02000009 and =SUBTOTAL(9,O10:O4236) which delivers a 0 result. the SUM answer is correct. This is so baffling and the first time I have come across it.

 

[Andy Pilkington]

Does this happen for a number of ranges in various worksheets or just one single range in one worksheet?

Perhaps you could give us a sample of data with XL2BB where this does happen so that we can take a look?

When this does happen, is the number produced by the SUM function a very small decimal number?

Get this, I've just converted the values in the SUM column to actual values rather than formulas and the SUBTOTAL function now delivers the correct answer. I don't know how to use XL2BB but have a simple extract of the data that produces these results that I could upload, but again not having done this before I don't know how to do this either. Not very helpful I know, any ideas?

 

[Fluff]

Do the formulae in that column also use the subtotal function?

 

[Andy Pilkington]

Do the formulae in that column also use the subtotal function?

Bingo, that's it, of course the values in that column will change when I filter the sheet.
Thanks for this, I just now need to find another way of achieving my aim.
Thanks Andy P

 

[RoryA]

Use AGGREGATE instead of SUBTOTAL?

 

[Andy Pilkington]

Use AGGREGATE instead of SUBTOTAL?

I'll give it a try, thanks.

 

[Peter_SSs]

I don't know how to use XL2BB

For the future, the steps are set out in a fair bit of detail if you follow that link I gave you in post #2 (or the one in my signature block below).