COUNTIF function query
 

I have a spreadsheet with some survey results on it. As I want the results to
be assigned to the person completing the survey, I have several entries in
one cell where there has been a multiple choice question. I have put each
answer on a separate line so it's clear there were multiple answers.

I am now trying to COUNTIF each multiple choice option to see which was the
most popular, however my sum seems unable to count answers that are not top
of the cell. Is it possible to create a sum which can count the options
anywhere in the cell?

I have tried putting a * before the criteria and that brought back slightly
better results but still missed some cells.

If you need any further information please holler.

Thanks,

Wendy


Example:

A B
1 John Smith Email marketing
Hard copy marketing
Promotional gifts
2 Bob Smith Hard copy marketing
etc.

COUNTIF function query
 

I have tried putting a * before the criteria and that brought back slightly
better results but still missed some cells.

Not sure whether you tried: =COUNTIF(B:B,"*"&D2&"*")
where D2 contains the text, eg: Hard copy marketing
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---

COUNTIF function query
 

ohmygod, of course - what an idiot. I didn't try the wildcard at the other
end as well! It's working perfectly now, thank you Max!

"Max" wrote:

I have tried putting a * before the criteria and that brought back slightly
better results but still missed some cells.

Not sure whether you tried: =COUNTIF(B:B,"*"&D2&"*")
where D2 contains the text, eg: Hard copy marketing
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---

COUNTIF function query
 

Assuming that you have entered ALT+ENTER in B1 so that B1 contains Email
marketing, Hard copy marketing, Promotional gifts try a couple of helper
columns.

C1: =IF(LEN(B1)=0,"",LEN(B1)-LEN(SUBSTITUTE(B1,CHAR(10),""))+1)
and copy down gives you the number of duties in the cell.

In D1 enter the Array formula:
=IF(ROWS($1:1)<=SUM((A$2:A$294<"")/COUNTIF(A$2:A$294,A$2:A$294&"")),INDEX(A$2:A$294,S MALL(IF(A$2:A$294<"",IF(ROW(A$2:A$294)-MIN(ROW(A$2:A$294))+1=MATCH(A$2:A$294,A$2:A$294,0) ,ROW(A$2:A$294)-MIN(ROW(A$2:A$294))+1)),ROWS($1:1))),"")

Array formulas are enter CSE (Ctrl + Shift + Enter) copy down until you get
a blank cell.
This gives the list of names in Ascending order (assuming that they have
been duplicated)

The totals in E1 given by the formula:
=SUMPRODUCT(--($A$1:$A$200=D1),($C$1:$C$200))

copy down

regards
Peter Atherton

"Wendy-Bob" wrote:

I have a spreadsheet with some survey results on it. As I want the results to
be assigned to the person completing the survey, I have several entries in
one cell where there has been a multiple choice question. I have put each
answer on a separate line so it's clear there were multiple answers.

I am now trying to COUNTIF each multiple choice option to see which was the
most popular, however my sum seems unable to count answers that are not top
of the cell. Is it possible to create a sum which can count the options
anywhere in the cell?

I have tried putting a * before the criteria and that brought back slightly
better results but still missed some cells.

If you need any further information please holler.

Thanks,

Wendy


Example:

A B
1 John Smith Email marketing
Hard copy marketing
Promotional gifts
2 Bob Smith Hard copy marketing
etc.

COUNTIF function query
 

Thanks Peter, that's really helpful (and way above anything I could ever have
worked out)!

The C1 formula that gives the number of duties in a cell; does that count
the number of seperate lines within a cell or can it count other multiples as
well (ie. separated by a comma instead)?

Thanks,

Wendy

"Billy Liddel" wrote:

Assuming that you have entered ALT+ENTER in B1 so that B1 contains Email
marketing, Hard copy marketing, Promotional gifts try a couple of helper
columns.

C1: =IF(LEN(B1)=0,"",LEN(B1)-LEN(SUBSTITUTE(B1,CHAR(10),""))+1)
and copy down gives you the number of duties in the cell.

In D1 enter the Array formula:
=IF(ROWS($1:1)<=SUM((A$2:A$294<"")/COUNTIF(A$2:A$294,A$2:A$294&"")),INDEX(A$2:A$294,S MALL(IF(A$2:A$294<"",IF(ROW(A$2:A$294)-MIN(ROW(A$2:A$294))+1=MATCH(A$2:A$294,A$2:A$294,0) ,ROW(A$2:A$294)-MIN(ROW(A$2:A$294))+1)),ROWS($1:1))),"")

Array formulas are enter CSE (Ctrl + Shift + Enter) copy down until you get
a blank cell.
This gives the list of names in Ascending order (assuming that they have
been duplicated)

The totals in E1 given by the formula:
=SUMPRODUCT(--($A$1:$A$200=D1),($C$1:$C$200))

copy down

regards
Peter Atherton

"Wendy-Bob" wrote:

I have a spreadsheet with some survey results on it. As I want the results to
be assigned to the person completing the survey, I have several entries in
one cell where there has been a multiple choice question. I have put each
answer on a separate line so it's clear there were multiple answers.

I am now trying to COUNTIF each multiple choice option to see which was the
most popular, however my sum seems unable to count answers that are not top
of the cell. Is it possible to create a sum which can count the options
anywhere in the cell?

I have tried putting a * before the criteria and that brought back slightly
better results but still missed some cells.

If you need any further information please holler.

Thanks,

Wendy


Example:

A B
1 John Smith Email marketing
Hard copy marketing
Promotional gifts
2 Bob Smith Hard copy marketing
etc.

COUNTIF function query
 

no prob, you're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"Wendy-Bob" wrote in message
...
ohmygod, of course - what an idiot. I didn't try the wildcard at the other
end as well! It's working perfectly now, thank you Max!

COUNTIF function query
 

You can change the substitute formula to look for a comma.

e.g C1: =IF(LEN(B1)=0,"",LEN(B1)-LEN(SUBSTITUTE(B1,",",""))+1)

Peter

"Wendy-Bob" wrote:

Thanks Peter, that's really helpful (and way above anything I could ever have
worked out)!

The C1 formula that gives the number of duties in a cell; does that count
the number of seperate lines within a cell or can it count other multiples as
well (ie. separated by a comma instead)?

Thanks,

Wendy

"Billy Liddel" wrote:

Assuming that you have entered ALT+ENTER in B1 so that B1 contains Email
marketing, Hard copy marketing, Promotional gifts try a couple of helper
columns.

C1: =IF(LEN(B1)=0,"",LEN(B1)-LEN(SUBSTITUTE(B1,CHAR(10),""))+1)
and copy down gives you the number of duties in the cell.

In D1 enter the Array formula:
=IF(ROWS($1:1)<=SUM((A$2:A$294<"")/COUNTIF(A$2:A$294,A$2:A$294&"")),INDEX(A$2:A$294,S MALL(IF(A$2:A$294<"",IF(ROW(A$2:A$294)-MIN(ROW(A$2:A$294))+1=MATCH(A$2:A$294,A$2:A$294,0) ,ROW(A$2:A$294)-MIN(ROW(A$2:A$294))+1)),ROWS($1:1))),"")

Array formulas are enter CSE (Ctrl + Shift + Enter) copy down until you get
a blank cell.
This gives the list of names in Ascending order (assuming that they have
been duplicated)

The totals in E1 given by the formula:
=SUMPRODUCT(--($A$1:$A$200=D1),($C$1:$C$200))

copy down

regards
Peter Atherton

"Wendy-Bob" wrote:

I have a spreadsheet with some survey results on it. As I want the results to
be assigned to the person completing the survey, I have several entries in
one cell where there has been a multiple choice question. I have put each
answer on a separate line so it's clear there were multiple answers.

I am now trying to COUNTIF each multiple choice option to see which was the
most popular, however my sum seems unable to count answers that are not top
of the cell. Is it possible to create a sum which can count the options
anywhere in the cell?

I have tried putting a * before the criteria and that brought back slightly
better results but still missed some cells.

If you need any further information please holler.

Thanks,

Wendy


Example:

A B
1 John Smith Email marketing
Hard copy marketing
Promotional gifts
2 Bob Smith Hard copy marketing
etc.