Count based on string in cell

J

jmaj

Using Excel 2007. I am not sure if I am trying to do is too ambitious.

My single column sheet looks something like this
A1
abc
abc, def
abc, ghi
def
def, ghi
abc, def, ghi
ghi

Possible values are abc, def, and ghi in any combination but always in
alpha order

I want to:

Chart the responses on
how many cells have only 'abc',
how many cells have only 'def''
how many cells have only 'ghi',
how many cells have 'abc' & 'def' & 'ghi'
how many cells have 'abc' & 'def'
how many cells have 'abc' & 'ghi'
how many cells have 'def' & 'ghi'


Is this possible without summarizing results first?

Assuming it was not, I tried to summarize the results first and then
chart them, but I am having trouble with the logic. I tried the
following with no luck:

To summarize 'abc' only responses =COUNTIF(A1:A7,isnumber(SEARCH
("abc",A1:A7)))
To summarize 'abc, def'' only responses =COUNTIF(A1:A7,isnumber(SEARCH
("abc, def",A1:A7)+SEARCH("ghi",A1:A7)))
etc



Any suggestions? Thanks in advance.

JL
 
J

jmaj

Here is one way. I put the data in A2:A8 and made the following entries:

B1:  abc
C1:  def
D1:  ghi
E1:  abc+def
F1:  abc+ghi
G1:  def+ghi
H1:  All
B2:  =--ISNUMBER(FIND(B$1,$A2))
Copy B2 to B2:D8
E2:  =IF(B2+C2=2,1,0)
F2:  =IF(B2+D2=2,1,0)
G2:  =IF(C2+D2=2,1,0)
H2:  =IF(B2+C2+D2=3,1,0)
Copy E2:H2 to rows 3:8
Sum each column in row 9 (these are your totals)

Hope this helps,

Hutch

Thanks! I will try this.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top