Getting true blank cell v ""

[Suzanne]

Good morning. In formulas i typically use a false value of "" to present or
store the result as a 'blank' cell. When i copy and paste the value of the
formulas and counta or countif range > 0, Excel counts this cell. Should i
be using something other than "" if my result is false and want the cell to
be empty/truly blank?

 

[Pete_UK]

A formula cannot return a truly blank result - the cell contains the
formula after all !

You can count blanks and formula blanks like this:

=COUNTIF(A1:A10,"")

Hope this helps.

Pete

 

[Gary''s Student]

COUNTA() counts these pseudo-empty cells. If you want to exclude them from a
tally, use COUNT() instead.

 

[Kevin B]

You can use the following array formula to count the number of cells not
equal to "":
Type the following formula, substituting the range B1:B8 with the range you
wish to count, but don't press enter just yet:

=SUM(IF(B1:B8<>"",1,0))

Then press <Ctrl>+<Alt>+<Enter> to enter the formual as an array

 

[Suzanne]

Thanks Gary!
--
Thank you -- Suzanne.

COUNTA() counts these pseudo-empty cells. If you want to exclude them from a
tally, use COUNT() instead.

 

[Cimjet]

Hi Kevin
I think you meant <Ctrl>+Shift>+<Enter>
Then press <Ctrl>+<Alt>+<Enter> to enter the formual as an array

 

[Dave Peterson]

Saved from a previous post:

If you want to see what's left in that cell after you convert ="" to values,
try:
Tools|Options|Transition Tab|Toggle Transition Navigation keys on.

Then select one of those cells and look at the formula bar. You'll see an
apostrophe. (Don't forget to toggle the setting to off.)

When I want to clean up this detritus, I do this:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all

============
Then your =counta() will return what you want. And the End|Arrow keys
(End|DownArrow and the like) will stop at the spots you expect.

 

[Kevin B]

You're so right Cimjet. Keyboarding without the correct dose of Monday
morning espresso can lead to mistakes not made at any other time.

 

[Cimjet]

I'm on my 4th cup of coffee.

You're so right Cimjet. Keyboarding without the correct dose of Monday
morning espresso can lead to mistakes not made at any other time.