Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Counting Letters in a Column
Hi folks, I have a spreadsheet where there is survey data that I need to begin analyzing. In several of the questions there were multiple choice answers that could be answered by any combination or just one letter. For example, answer 3 has many that have ABD, ABC, BCD, etc.. For analysis purposes I want to start first with the number of A's, B's, C's etc, then look at the unique combinations. Can "countif" count text entries, if so what would the formula look like? Thanks, PZan -- PokerZan ------------------------------------------------------------------------ PokerZan's Profile: http://www.excelforum.com/member.php...o&userid=23480 View this thread: http://www.excelforum.com/showthread...hreadid=386475 |
#2
|
|||
|
|||
Here are 2 ways, using cells A1:A14 Choices A B C AB BC AC A B C AB BC ABC AC =COUNTIF($J$1:$J$14,"A") Returns the count of just A's(2) =COUNTIF($J$1:$J$14,"ABC") Returns the count of ABC's (1) OR If you use a Pivot Table, it will return a list of all unique combinations in the list with their count: DataPivot TableExcel List...[Next] Select the range A1:A13...[Next] Click [Layout] ---ROW: Drag "Choices" ---DATA: Drag "Choices" (it will change to "Count of Choices") Click [OK] Check "Existing Worksheet" and select a cell to place the table in Click [Finish] It will return something like this: Count of Choices ---------------------- Choices___Total A_________2 AB________2 ABC_______1 AC________2 B_________2 BC________2 C_________2 Will either of those help? Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=386475 |
#3
|
|||
|
|||
Not really, what I am looking for is a count of just A's that would include ABC, so in your example I would like a formula that would return "7" as the cells that contain any "A". I know about the pivot table option and that is my "plan B" if I need to use it but I would prefer a formula driven approach. Thanks, PZan -- PokerZan ------------------------------------------------------------------------ PokerZan's Profile: http://www.excelforum.com/member.php...o&userid=23480 View this thread: http://www.excelforum.com/showthread...hreadid=386475 |
#4
|
|||
|
|||
Ok...Try this: For the list in A1:A14 . . . Choices A B C AB BC AC A B C AB BC ABC AC =COUNTIF($A$2:$A$14,"*A*") That will return the number of cells that contain A. Does that help? Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=386475 |
#5
|
|||
|
|||
BINGO! Thanks for the help! PZan -- PokerZan ------------------------------------------------------------------------ PokerZan's Profile: http://www.excelforum.com/member.php...o&userid=23480 View this thread: http://www.excelforum.com/showthread...hreadid=386475 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
why have column letters changed to numbers in excel | Excel Discussion (Misc queries) | |||
how do i prefix a column with letters | Excel Discussion (Misc queries) | |||
column headings in numbers vs letters | Excel Discussion (Misc queries) | |||
row numbers & column letters | Excel Discussion (Misc queries) | |||
How do I change column labels from numbers to letters in Excel? | Excel Discussion (Misc queries) |