count - but exclude counting cells with a formula

  • Thread starter Thread starter Gotroots
  • Start date Start date
G

Gotroots

What formula do I need to count a range where the cells contain a formula.
The cell range formula returns a text value.

example:

A1:A10 contain a formula that will return a text result

A11 contains a formula to return the number of cells that have a result in
in A1:A10 Any formulas I have tried count the formulas also.
 
Assume that you are having value in A Column Like this..

A Column
ABC
DEF
5646
ZYX
65466


Try this formula in B1 cell for finding the Text Values from A1:A5.
=COUNTA(A1:A5)-COUNT(A1:A5)

=COUNTA(A1:A5) will count the Total number cells which is having any value
from A1:A5

=COUNT(A1:A5) will count the Total number of cells which is having the
Numeric Values from A1:A5

But the Counta function will count the cells which is having only the space
also.

If this post helps, Click Yes!
 
Thank you for the help.

this the test formula I tried:

=COUNTA(A1:A11)-COUNT(A1:A10)

A3 is the only cell that contains a text value

the formula returned 11

when it should have only returned 1

the formula seems to count all the cells containing a formula and adds any
that has a value returned by them





this the formula
 
Hi Gotroots,

What do you get in the cell when there is no text result?

If you get a blank try:

=COUNTA(A1:A10)-COUNTIF(A1:A10,"")

This counts all entries then subtracts the blanks.

The entry "" at the end can be changed to whatever you get when there is no
text.

Squeaky
 
That sorted that one!

Thanks Squeaky

Squeaky said:
Hi Gotroots,

What do you get in the cell when there is no text result?

If you get a blank try:

=COUNTA(A1:A10)-COUNTIF(A1:A10,"")

This counts all entries then subtracts the blanks.

The entry "" at the end can be changed to whatever you get when there is no
text.

Squeaky
Click to expand...
 
Back
Top