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

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

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

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

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