Count based on string in cell

  • Thread starter Thread starter jmaj
  • Start date Start date
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
 
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
 
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
Click to expand...

Thanks! I will try this.
 
Back
Top