Thanks:  0
Likes:  0

1. I have the following

Cell A1 "Failed"
Cell A2 "Passed"
Cell A3 "No-Show"

I have a spreadsheet with 192 entries using the above words. How could I add them up at the end, come up with a number, for how many failed, passed, and no-show? In other words, how could I add cells that have words or names?

Thanks!

2. click on help, type in countif, click on search...

3. =countif(A1:A3,"Failed")

4. =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 ]

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•