=COUNTIF(A1:A13,"Failed") & " Failed, " & COUNTIF(A1:A13,"Passed") & " Passed, " & COUNTIF(A1:A13,"No-Show") & " No-Shows, " & COUNTIF(A1:A13,"") & " Blanks"
Where A1:A13 is the range of your data.
The result will be of the form:
# Failed, # Passed, # No-Shows and # Blank
all in one cell.
And;
=COUNTIF(A1:A13,"Failed")&" Failed, "&COUNTIF(A1:A13,"Passed")&" Passed, "&COUNTIF(A1:A13,"No-Show")&" No-Shows, "&COUNTIF(A1:A13,"")&" Blanks and " & 13 - (COUNTIF(A1:A13,"Failed")+COUNTIF(A1:A13,"Passed")+COUNTIF(A1:A13,"No-Show")+COUNTIF(A1:A13,"")) & " Others"
Will give the form:
# Failed, # Passed, # No-Shows, # Blanks and # Others
Note: the "13-" in the "Others" portion of the above formula, is the number of cells (Rows) in your data table!
In place of "13-" in the above formula you can use:
Rows(A1:A13)-
This will automatically count all the rows(cells) in your range even if the length of your data table changes.
Like:
=COUNTIF(A1:A13,"Failed")&" Failed, "&COUNTIF(A1:A13,"Passed")&" Passed, "&COUNTIF(A1:A13,"No-Show")&" No-Shows, "&COUNTIF(A1:A13,"")&" Blanks and " & ROWS(A1:A13) - (COUNTIF(A1:A13,"Failed")+COUNTIF(A1:A13,"Passed")+COUNTIF(A1:A13,"No-Show")+COUNTIF(A1:A13,"")) & " Others"
And;
=(COUNTIF(A1:A13,"Failed")+COUNTIF(A1:A13,"Passed")+COUNTIF(A1:A13,"No-Show"))
Will give the total count of all cells that contain, "Failed, Passed and/or No-Show" as one number, for the total of all three of these test words, found in your range.
Hope this helps. JSW
This message was edited by Joe Was on 2002-05-03 19:36
This message was edited by Joe Was on 2002-05-03 19:36
This message was edited by Joe Was on 2002-05-03 19:50
This message was edited by Joe Was on 2002-05-03 19:53
This message was edited by Joe Was on 2002-05-03 20:03
This message was edited by Joe Was on 2002-05-03 20:10