COUNTIF function query

[Wendy-Bob]

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.

 

[Max]

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
---

 

[Wendy-Bob]

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!

 

[Billy Liddel]

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,SMALL(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]

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

 

[Max]

no prob, you're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik

 

[Billy Liddel]

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