Use COUNTIF to count another formulas output

[barryweston]

Hello!

I'm hoping someone can help me solve a likely simple problem that is a bit outside my skill level.

I am trying to use COUNTIF to count the number of cells on another sheet that have a value of "TRUE".

The formula is =COUNTIF('Sheet1'!AI:AI,"TRUE"). However, it returns a 0.

If I manually add the word TRUE to that column, it will return the value.

I assume COUNTIF needs some other flag to read the formula's output from column AI, and not read the text of the formula itself.

Any help?

Thank you.

[Jonmo1]

Try removing the quotes from "TRUE"

[barryweston]

Thanks for the reply. Still returns a 0, not the actual value.

Essentially I need to find a way to count or reference a formula's output, not the formula itself.

[Jonmo1]

That's what formulas do, they look at the result (output) of other formulas.

What is the actual formula you have in Sheet1 column AI ?

[Jonmo1]

The words True and False are special keywords in Excel. A Boolean value result from a calculation with a binary True or False value.
The countif function is looking for that value (despite having " marks around it).
If your formula in column AI is returning an actual TEXT String with quotes like
=IF(A1=5,"TRUE")
Then countif won't count it.

You would need to make that formula return a true Boolean (without the quotes)
=IF(A1=5,TRUE)
Or just simply
=A1=5

[MrShorty]

Agree with Jonmo1 -- more information is needed. I can kind of replicate the problem. I:

1) Entered =RANDBETWEEN(0,1)=1 into a dozen cells to get a set of random boolean TRUE/FALSE values (are your values boolean values or text strings?)
2) using this range, =COUNTIF(A3:A25,TRUE) correctly counted the number of boolean TRUE values.
3) Converted to text in another column =IF(A3,"TRUE","FALSE")
4) On tjhis range, the COUNTIF() function failed. =COUNTIF(B3:B25,"TRUE") returned 0.

So my best guess at this point is that your boolean values are actually text strings, and COUNTIF() does not want to recognize that particular text string (I would guess that COUNTIF() internally converts "TRUE"/"FALSE" text strings to the actual boolean values. If this is indeed what is happening in your sheet, I recommend either choosing a different text string ("Yes"/"No") maybe, or use the actual boolean values TRUE/FALSE (and not text strings).

[barryweston]

What I have is this:

=IF(AND(AF2="Yes",OR(AG2="Yes",AH2="Yes")),"TRUE","FALSE".
So The formula in AI looks in AF2, AG2, AH2 for the word Yes in any of the three cells and will display TRUE if they have a Yes or False if they are all No.

I want to show how many times on another dashboard style worksheet that the number of TRUE from AI occurs.

Does that make sense?

[barryweston]

So what you said about the use of True and False struck a chord with me.

I changed the formula in AI to output Yes and No, and used =COUNTIF('Sheet1'!AI:AI,"Yes") and it returned a correct value, in this case 37.

Thanks for your help everyone!

[FDibbins]

As has already been mentioned, remove the quotes from around the TRUE and FALSE

[Jonmo1]

I would do it like this
=AND(AF2="Yes",OR(AG2="Yes",AH2="Yes"))
This will return the more desirable and usable aforementioned Boolean True or False results

Then your original formula (with quotes removed) will work.