COUNTIF = "True" as string, not boolean

[STEVEMILLS04]

I run a report from our HR system that outputs a True/False column as a string (verified using =TYPE() formula). When attempting to do the countif formula below on the column it does not count them:

=COUNTIF(TermList[Is Voluntary?],"False")

It works when I change the column to a Boolean and change the formula to FALSE rather than "False" but this would not be my preferred method because I don't want to have to edit the column every time I run the report.

Any tips?

 

[JackDanIce]

Try:

=SUMPRODUCT(--(LEN(termLists[Is Voluntary?])=5))

This will fail if other string values exist that are not "False" but are (also) 5 characters in length

 

[Jonmo1]

I don't think there's any way around that with countif (or sumif/averageif).
When it see's "False" as your criteria argument, it converts it to the Boolean False
And there are no Boolean False's in the range, so it won't count.

Try
=SUMPRODUCT(--(TermList[Is Voluntary?]="False"))

 

[STEVEMILLS04]

Thanks Jack, I should have mentioned that my formula is actually a much longer IF/COUNTIFS formula:

=IF(Month>=C2,COUNTIFS(TermList[Location ID],$A2,TermList[Job Title],$D2,TermList[Term Month],MONTH($C2),TermList[Term Year],YEAR($C2),TermList[Is Voluntary?],"False"),0)

 

[RoryA]

If you only have true/false in there, then you could use:

=COUNTIF(TermList[Is Voluntary?],"False*")

 

[Jonmo1]

I might suggest converting that column of Textual True/False values to actual Booleans...

Highlight the column, go to Data - Text To Columns
Select Deliminated - Next
UNcheck all options
Finish.

 

[JackDanIce]

@Jonmo1's suggestion will account for value of "False" only, can you adapt their suggestion into your longer formula?

Jonmo1 re data conversion:

but this would not be my preferred method because I don't want to have to edit the column every time I run the report.

 

[STEVEMILLS04]

If you only have true/false in there, then you could use:

=COUNTIF(TermList[Is Voluntary?],"False*")

What? That actually worked... But why?

 

[Jonmo1]

What? That actually worked... But why?

Yes, that was a great solution, thanks Rory

The Wildcard(*) forces countif to treat that argument as a TEXT string, instead of converting it to Boolean.

 

[STEVEMILLS04]

Genius, EXACTLY what I was looking for.

Thanks!!