Subtotal Mystery

[EU]

I added added a SUBTOTAL with COUNTA sub_function
=SUBTOTAL(3,range) and it counts ALL cells in that range
instead of just the non-empty ones. So, if there are
15,000 non-empty cells in that range of 20,000 it returns
the total COUNT 20,000 cells (both non-empty and empty)
instead of just the cells with values in them.

I used the same SUBTOTAL with COUNTA subfunction on
another sheet in the same workbook and it works fine. Why
is this function behaving this way? What do I need to do
to fix it. I tried erasing and deleting all rows beneath
the data to "clean" the sheet but this did not correct the
problem.

Thanks for your valuable advice in advance.

EU

 

[Peo Sjoblom]

Do you have spaces in the cells?

 

[EU]

Thanks Peo--good suggestion. I did a "text to columns"
scrub to eliminate spaces but I still have the problem.

This is mind-boggling and very frustrating...

EU

 

[Ken Wright]

If you do =ISBLANK(??) against the cells that are supposed to be empty, what do you get, TRUE or
FALSE??

 

[Dave Peterson]

Any chance your range consisted of formulas (some evaluating to "") and you did
copy|paste special|values?

If yes, then those cells with ="" converted to values are not empty cells.

Select your range and change
(leave blank)
to
$$$$$$$ (a unique string)
Then replace all

Then do it in reverse
$$$$$$$
to
(leave blan)
replace all.

did that help?