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

Try this:

=COUNTIF(A1:A100,"PASS")

Adjust the range to fit your needs.

HTH,
Elkar


"Samuel" wrote:

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

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

=COUNTIF(A:A,"PASS")

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Samuel" wrote in message
oups.com...
Pretty cool.
But since I don't know how many rows, is it possible to do the entire
column?
Thank you.

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" wrote:

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

Thank you!

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

Vaya con Dios,
Chuck, CABGx3



"Samuel" wrote:

Thank you!

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="&COUN TIF(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?

Try this, see if it makes a difference:

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

HTH,
Elkar


"Samuel" wrote:

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="&COUN TIF(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?

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" wrote:

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="&COUN TIF(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?

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

--
Kevin Backmann


"Samuel" wrote:

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