cell formula for counting instances of text?

[Samuel]

I have a cell that could have PASS, FAIL, or N/A
What kind of formula can I use to count the number of times PASS FAIL
or N/A occurs in the column and write the count to a header cell?
I can do math to sum, avg, etc, but I am not sure how to deal with
text.
Thank you,
Sam

 

[Guest]

Try this:

=COUNTIF(A1:A100,"PASS")

Adjust the range to fit your needs.

HTH,
Elkar

 

[Samuel]

Pretty cool.
But since I don't know how many rows, is it possible to do the entire
column?
Thank you.

 

[Guest]

If you want the answer all in one cell.........

="Pass= "&COUNTIF(A:A,"pass")&CHAR(10)&"Fail=
"&COUNTIF(A:A,"fail")&CHAR(10)&"N/A= "&COUNTIF(A:A,"n/a")

Format for "wrap text"

Vaya con Dios,
Chuck, CABGx3

 

[Samuel]

Thank you!

 

[Guest]

Happy to help, thanks for the thanks.........

Vaya con Dios,
Chuck, CABGx3

 

[Bob Phillips]

=COUNTIF(A:A,"PASS")

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Samuel]

OH. man, I hate to say this. These formulas returns a zero.
I tried:
=COUNTIF(K1:K100,"FAIL")
and
="Pass="&COUNTIF(K:K,"PASS")&CHAR(10)&"Fail="&COUNTIF(K:K,"FAIL")&CHAR(10)&
"N/A=" &COUNTIF(K:K,"N/A")

Since both return a zero, and I can clearly see the values in column K,
I assume there is something else I have not considered....
As far as I can tell there is no formatting in the cells, and no
unprintable characters. What could I be missing?

 

[Guest]

Try this, see if it makes a difference:

=COUNTIF(K1:K100,"*FAIL*")

HTH,
Elkar

 

[Guest]

This UDF will do what you want and return a string that looks like the
following:

Pass=#/Fail = #/ NA = #

Where # = the number of occurrences for that value.

Press Alt+F11 to open the VBE, insert a module and paste the following
function in.

Use it like you would any other function.

=PASSFAILNA(A1:A10)

The line Application.Volatile will force the formula to recalc with each
recalculation of the spreadsheet.

Function PassFailNA(rng As Range) As String

Dim intPass As Integer
Dim intFail As Integer
Dim intNA As Integer
Dim r As Range
Dim lngCells As Long
Dim varVal As Variant

Set r = rng
Application.Volatile

For lngCells = 1 To r.Cells.Count
varVal = r.Cells(lngCells)
Select Case varVal
Case Is = "Pass"
intPass = intPass + 1
Case Is = "Fail"
intFail = intFail + 1
Case Is = "N/A"
intNA = intNA + 1
Case Else
End Select
Next lngCells

PassFailNA = "Pass = " & intPass & "/Fail = " & _
intFail & "/NA = " & intNA

Set r = Nothing
Exit Function

End Function

 

[Guest]

I just tried both formulas again in XL97SR2 and both work fine. Perhaps your
"Pass"/"Fail"/"N/A" data is corrupt (a leading space will cause the formulas
to fail)........put the formulas in place and try overwriting a few cells in
your K1:K100 range.......

Vaya con Dios,
Chuck, CABGx3

 

[Samuel]

Thank you,
=COUNTIF(K1:K100,"*FAIL*") from Elkar worked.
I assume that the * are wild cards and this is actually matching any
text with 'FAIL' in it..?
But it works,
Thanks all!

 

[Guest]

That's great you got it working, and yeah, the *FAIL* will overcome leading
spaces, etc.....
This mod will work as well, if you want all the answers in one cell.....
="Pass="&COUNTIF(K:K,"*PASS*")&CHAR(10)&"Fail="&COUNTIF(K:K,"*FAIL*")&CHAR(10)&"N/A= "&COUNTIF(K:K,"*N/A*")

Vaya con Dios,
Chuck, CABGx3