If the column does not contain numbers formatted as numbers then this will work and can be copied / pasted down next to your data as needed:
=IF(ISERROR(NOT(A1)),COUNTIF(A:A,A1),SUM(IFERROR(1*NOT(IF(A1,NOT(A:A),A:A&"")),0)))
The explanation is messy because the solution is messy.
NOT(A1)
returns an error for strings except the magic true
and false
as other answers mentioned.
If that returns an error, then it's a regular string and we can use a regular COUNTIF()
.
If there isn't an error, then it's either a number or true / false.
This is where it gets messy.
... SUM(IFERROR(1*NOT(IF(A1,NOT(A:A),A:A&"")),0)) ...
SUM(IFERROR(1*NOT(~),0))
will create an array of 1 and 0 then sum the array. If NOT(~)
is false or an error, the array is 0
at that location. Only if NOT(~)
is true will it return 1
. So let's break down that ~
. If we've gotten this far in the formula, we know the value in A1
is either true or false (or a number, which is why it's important your data not have numbers formatted as numbers).
- If
A1
is true, we want NOT(A:A)
because that will turn all the true values into false values which will be inverted again by the NOT(~)
that it's wrapped in.
- If
A1
is false, we want A:A&""
because, without the &""
, we'll end up counting all the blank cells, too. (NOT([blank]) = TRUE
but NOT("") = #VALUE!
because it's a string.)
=COUNTIF(A1:A5,"<truf")-COUNTIF(A1:A5,"<=trud")
counts every string that begins with “true” or “trud”, such as “trudge”.LET