Count based on string in cell
 

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

Count based on string in cell
 

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

"jmaj" wrote:

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
.

Count based on string in cell
 

On Dec 2, 12:24*pm, Tom Hutchins
wrote:
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

"jmaj" wrote:
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
.

Thanks! I will try this.