Counting Letters in a Column

[PokerZan]

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

[Ron Coderre]

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:

Data>Pivot Table>Excel 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

[PokerZan]

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

[Ron Coderre]

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

[PokerZan]

BINGO!

Thanks for the help!

PZan