Subtotal Mystery

  • Thread starter Thread starter EU
  • Start date Start date
E

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
 
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
 
If you do =ISBLANK(??) against the cells that are supposed to be empty, what do you get, TRUE or
FALSE??
 
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?
 
Back
Top