How to count cells which contain a formula that returns TRUE

[Art]

The question in princilpe refers to any cell that contain formula.I\ve
noticed that if I use COUNTIF on cells that contains formula returning TRUE
it doesn't count.It count only if I simply type TRUE in the cells

 

[Gary''s Student]

In A1 thru A7 enter:
=1=1
In A8 thru A10 enter:
=1=2

We see in A1 thru A10:
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
FALSE
FALSE
FALSE

in another cell enter:
=COUNTIF(A1:A10,TRUE)
which correctly displays 7

 

[Jacob Skaria]

MAke sure your formulas which return TRUE returns the boolean TRUE and not a
text (within double quotes) like

To count on formulas like this one you need to use COUNTIF(a:a,"true")
=IF(A1=0,"true","false")


To count on formulas which return boolean you can try COUNTIF(a:a,TRUE)
=IF(A1=0,TRUE,FALSE)


If this post helps click Yes

 

[David Biddulph]

Are you sure that your formula is returning the boolean value TRUE and not
the text string "TRUE" ?

 

[Art]

It works in your example, but not in my particular case
I'm using:
=IF(AND(C181=1,G181=0,J181=1), "TRUE", "")
this returns TRUE or nothing.
I just want simply count the cells returning TRUE.
Grrrr
Thanks,

 

[Ron Rosenfeld]

The question in princilpe refers to any cell that contain formula.I\ve
noticed that if I use COUNTIF on cells that contains formula returning TRUE
it doesn't count.It count only if I simply type TRUE in the cells

Post your formula. There is likely some error in how you are trying to do
this, as I cannot reproduce your problem here.
--ron

 

[Art]

I've tried both formulas:
=IF(A1:A10,TRUE)
and
=IF(A1:A10,"true")
Both give 0 in my case.
Thanks,

 

[Art]

=IF(AND(C181=1,G181=0,J181=1), "TRUE", "")
this returns TRUE or nothing.
I just want simply count the cells returning TRUE.

I've tried both formulas:
=COUNTIF (A1:A10,TRUE)
or
=COUNTIF(A1:A10,"TRUE")
Both give 0 in my case.
Thanks,

 

[David Biddulph]

Your formula is producing the text string "TRUE", not the logical value
TRYE. Get rid of the quote marks.

 

[David Biddulph]

Are you sure that your COUNTIF formulae work, Jacob? For me, with Excel
2003, it seems to treat both as looking for the boolean, not the text
string.

 

[Jacob Skaria]

Oops...you are right we cannot and will have to use
=SUMPRODUCT(--(A1:A10="TRUE"))

If this post helps click Yes

 

[Art]

I've got it working,but ...
The formula returns text string,so I changed it to e.g Yes
=IF(AND(C181=1,G181=0,J181=1), "Yes", "")

And now using
=COUNTIF (A1:A10,Yes)
it works!

But as I mentioned before with the formula:
=IF(AND(C181=1,G181=0,J181=1), "TRUE", "")

and
formulas:
=COUNTIF (A1:A10,TRUE)
or
=COUNTIF(A1:A10,"TRUE")
Both give 0 in my case.

Thank you for your quick replies.
Art

 

[Jacob Skaria]

Try the below array formula which should count both text "TRUE" and boolean
TRUE

=SUM(IF(IF(F1:F20<>"",F1:F20&"A")="TRUEA",1,0))

If this post helps click Yes

 

[T. Valko]

it seems to treat both as looking for the boolean, not the text string.

To count TEXT "true":

=COUNTIF(A1:A10,"*true")

 

[Ron Rosenfeld]

=IF(AND(C181=1,G181=0,J181=1), "TRUE", "")
this returns TRUE or nothing.
I just want simply count the cells returning TRUE.

I've tried both formulas:
=COUNTIF (A1:A10,TRUE)
or
=COUNTIF(A1:A10,"TRUE")
Both give 0 in my case.
Thanks,

This is a fascinating finding.

One simple way around it is to change your IF formula to return the Boolean:

=IF(AND(C181=1,G181=0,J181=1), TRUE, "")

COUNTIF will then work as designed.

I think what may be happening is that COUNTIF is changing what should be the
text string "TRUE" into the Boolean, because both of these formulas seem to
work, so long as you change your IF formula to return a BOOLEAN TRUE:

=COUNTIF(A1:A10,"TRUE")
=COUNTIF(A1:A10,TRUE)

--ron

 

[Ron Rosenfeld]

=IF(AND(C181=1,G181=0,J181=1), "TRUE", "")
this returns TRUE or nothing.
I just want simply count the cells returning TRUE.

I've tried both formulas:
=COUNTIF (A1:A10,TRUE)
or
=COUNTIF(A1:A10,"TRUE")
Both give 0 in my case.
Thanks,

And, of course, you could also change the COUNTIF formula:

=COUNTIF(A1:A10,"*TRUE")
--ron