# SUBTOTAL <> SUM [No filters]

#### Andy Pilkington

##### Board Regular
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

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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?

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.

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?

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

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

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).

Replies
10
Views
252
Replies
3
Views
525
Replies
0
Views
86
Replies
2
Views
56
Replies
5
Views
218

1,203,744
Messages
6,057,116
Members
444,905
Latest member
Iamtryingman

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back