COUNTA Confusion....

[mark.wolven]

I know that I'm doing something wrong, because this has to be easier
than I'm making it out to be.

In a column of data from G1:G218, I have some text based information.
They info is grouped, as such, there are empty/blank cells in column G
where the groupings change.

Using COUNTA(G1:G218) = 215
Using COUNTBLANK(G1:G218) = 15

Which makes me think that the number of populated cells is 203 which I
get by taking 218 (all cells) and subtracting 15 (the countblank
answer).

What am I missing here? What does the 215 represent from the COUNTA
formula?

 

[Tyro]

You may have the situation where formulas in your range return the empty
("") string. Both COUNTA and COUNTBLANK include that in their count.
The 215 represents the number of non-empty cells. See Excel help for more
information on both functions.

Tyro

 

[mark.wolven]

You may have the situation where formulas in your range return the empty
("") string. Both COUNTA and COUNTBLANK include that in their count.
The 215 represents the number of non-empty cells. See Excel help for more
information on both functions.

Tyro

That's the thing that usually gets me, but there's no formulas here.
I've examined the cells, and they appear to be empty. Clicking on the
cell, and hitting Backspace doesn't change the results.

The Help for COUNTA doesn't offer the technical detail that I am
looking for. It appears to me to be counting cells that are blank as
being populated.

Now, I can look at this column all that I want and make a manual count
and be happy. But I've got 20+ worksheets in this book, and I am
trying to compare the values in this column in a summary sheet by
using the COUNTA function, but if I cannot trust the results of
COUNTA, I'll have to manually count all of these myself. It's easy
enough to append COUNTBLANK to the COUNTA formula, but since the
ranges are of various sizes, it won't work.

 

[T. Valko]

COUNTA will count all cells that are *not empty*. If a cell contains a
formula that returns a formula blank ("") COUNTA *will* count this cell.
COUNTBLANK will count *both* empty cells and cells that contain formula
blanks ("").

So, if you want a count of all cells that contain something that you can
actually see (assuming you don't have conditional formatting set to "hide"
anything and/or the cell will not contain only space characters):

=COUNTIF(G1:G218,"?*")

 

[Tyro]

Just for grins, put this formula in H1, you can insert a column if you need
to: =LEN(G1) and drag down through G218. If the cells are empty or contain a
formula that returns the empty string, the result will be 0 in each cell. If
there is anything in the cells, such as a space or any non-displayable
character, there will be a non-zero results

Tyro

 

[Tyro]

Correction: Drag down through H218, not G218

Tyro

 

[Tyro]

You can use COUNTA. COUNTA counts everything except cells that are truly
empty. It includes cells that have formulas that have returned the empty
string and appear to be empty. =COUNTIF(G1:G218,"?*") counts everything
except cells with formulas that have returned the empty string and cells
containing numbers. COUNT counts cells containing numbers. If you wish to
count all cells except those with formulas that have returned the empty
string, you can use: =COUNT(G1:G218,"?*") + COUNT(G1:G218). To determine the
number of truly empty cells, use =ROWS(G1:G218)-COUNTA(G1:G218). Leave
COUNTBLANK out of it as it includes those cells with formulas that have
returned the empty string. You should not trust COUNTA - COUNTBLANK to give
you the number of non-empty cells.

Tyro

 

[Tyro]

Correction: =COUNT(G1:G218,"?*") + COUNT(G1:G218) should be:
=COUNTIF(G1:G218,"?*") + COUNT(G1:G218)

 

[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