Adding Cells with Words

gymwrecker · May 3rd, 2002, 07:05 PM

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!

Tom Schreiner · May 3rd, 2002, 07:10 PM

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

Anne Troy · May 3rd, 2002, 07:21 PM

=countif(A1:A3,"Failed")

Joe Was · May 3rd, 2002, 08:33 PM

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

May 3rd, 2002, 07:21 PM	#3
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,512	=countif(A1:A3,"Failed") __________________ ~Anne Troy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 3rd, 2002, 07:05 PM	#1
gymwrecker Board Regular Join Date: Apr 2002 Location: Fort Knox, KY Posts: 249	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!

May 3rd, 2002, 07:10 PM	#2
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	click on help, type in countif, click on search...

May 3rd, 2002, 08:33 PM	#4
Joe Was MrExcel MVP Join Date: Feb 2002 Location: Central Florida, USA Posts: 7,541	=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 ]