How to count cells which contain a formula that returns TRUE

  • Thread starter Thread starter Art
  • Start date Start date
A

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
 
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
 
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
 
Are you sure that your formula is returning the boolean value TRUE and not
the text string "TRUE" ?
 
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,
 
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
Click to expand...

Post your formula. There is likely some error in how you are trying to do
this, as I cannot reproduce your problem here.
--ron
 
I've tried both formulas:
=IF(A1:A10,TRUE)
and
=IF(A1:A10,"true")
Both give 0 in my case.
Thanks,
 
=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,
 
Your formula is producing the text string "TRUE", not the logical value
TRYE. Get rid of the quote marks.
 
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.
 
Oops...you are right we cannot and will have to use
=SUMPRODUCT(--(A1:A10="TRUE"))

If this post helps click Yes
 
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
 
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
 
it seems to treat both as looking for the boolean, not the text string.

To count TEXT "true":

=COUNTIF(A1:A10,"*true")
 
=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,
Click to expand...

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
 
=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,
Click to expand...


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

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