count - but exclude counting cells with a formula
 

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.

count - but exclude counting cells with a formula
 

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!

--------------------
(Ms-Exl-Learner)
--------------------



"Gotroots" wrote:

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.

count - but exclude counting cells with a formula
 

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
"Ms-Exl-Learner" wrote:

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!

--------------------
(Ms-Exl-Learner)
--------------------



"Gotroots" wrote:

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.

count - but exclude counting cells with a 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

"Gotroots" wrote:

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.

count - but exclude counting cells with a formula
 

That sorted that one!

Thanks Squeaky

"Squeaky" wrote:

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

"Gotroots" wrote:

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.