Count If Issue

[russelgreen]

Hi all,

I'm trying to create an spreadsheet which counts certain words in a cell only once and gives me a total. I can do this but some of the words I'm searching for are part of another word that I'm searching (eg room and room service).

So this spreadsheet will look at company reviews and count how many times the key words are mentioned. How can search for one and it doesn't count as part of the other search?

Regards,

Russ

 

[Joe4]

So are you only looking for exact matches then?
Could you post a small sampling of data to show various examples of records that you do and do not want to count?

 

[russelgreen]

Thanks Joe,

yes I’m looking for exact matches but only count one in each cell.

so I will post a full review in one cell and want it to count each of the criteria. For most of them it works but I’ve got a search for room and one for service and then room service. I don’t want it too count under room or service if it’s for room service. I also have bathroom and bath and only want to count one or the other...

 

[Joe4]

yes I’m looking for exact matches but only count one in each cell.

I find that statement a bit confusing. If it *truly* is an exact match, it cannot count it anymore than once, by definition.

I would really like to see an example, showing various scenarios you may actually have, and what you expect your output to be.
Sometimes a picture makes what you are trying to do much clearer, especially if we can see what the data you are working with really looks like.
You can post screen images using this tool here: XL2BB - Excel Range to BBCode

 

[russelgreen]

Thanks Joe...

Master Feedback 2020.xlsx
	J
11
11 Nov

 

[Joe4]

Thanks Joe...

So it lools like your attempt to post data didn't quite work out.
Note that you can try it out in the "Test Here" forum (Test Here), to make sure you have it working correctly before posting it here.
That "Test Here" forum is a "playground" for you to test posting various things.

 

[russelgreen]

Sorry, lets try this...

So it lools like your attempt to post data didn't quite work out.
Note that you can try it out in the "Test Here" forum (Test Here), to make sure you have it working correctly before posting it here.
That "Test Here" forum is a "playground" for you to test posting various things.

Master Feedback 2020.xlsx
	A			D	E	F	G	H	I	J	K
1	Positve Feedback
2				Total
3	B&B			0
4	Bar			0
5	Bathroom			0
6	Bed			0
7	Breakfast			0
8	Building			0
9	Clean			0
10	Comfort			0
11	decour			0
12	Facilities			0
13	Heating			0
14	Food			0
15	Milk			0
16	Friendly			0
17	Furnishings			0
18	Location			0
19	Room			1
20	Service			0
21	Shower			0
22	Staff			0
23	Toiletries			0
24	Traders			0
25	TV			0		0	0
26	Welcome			0
27	Drinks			0
28	Concierge			0
29	Parking			0
30	Bank			0
31	Reception			0
32	Reservation			0
33	Room Service			0
34	Housekeeping			0
35	Scent			0		0	0
36	Bath			0
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51	Possitive Feedback
52	Name			Area	Feedback
53					Room
54
55
56
57
58
59
60
11 Nov

Cell Formulas
Range	Formula
F25	F25	=COUNTIF(E53:E253,"*tv*")
G25	G25	=COUNTIF(E53:E253,"*television*")
F35	F35	=COUNTIF(E53:E253,"*scent*")
G35	G35	=COUNTIF(E53:E253,"*smell*")
D3	D3	=COUNTIF(E53:E253,"*B&B*")
D4	D4	=COUNTIF(E53:E253,"*bar*")
D5	D5	=COUNTIF(E53:E253,"*"&A5&"*")
D6	D6	=COUNTIF(E53:E253,"*Bed*")
D7	D7	=COUNTIF(E53:E253,"*Breakfast*")
D8	D8	=COUNTIF(E53:E253,"*Building*")
D9	D9	=COUNTIF(E53:E253,"*clean*")
D10	D10	=COUNTIF(E53:E253,"*comfort*")
D11	D11	=COUNTIF(E53:E253,"*decour*")
D12	D12	=COUNTIF(E53:E253,"*facilities*")
D13	D13	=COUNTIF(E53:E253,"*heating*")
D14	D14	=COUNTIF(E53:E253,"*food*")
D15	D15	=COUNTIF(E53:E253,"*milk*")
D16	D16	=COUNTIF(E53:E253,"*friendly*")
D17	D17	=COUNTIF(E53:E253,"*furnishing*")
D18	D18	=COUNTIF(E53:E253,"*location*")
D19	D19	=COUNTIF(E53:E253,"*"&A19&"*")
D20	D20	=COUNTIF(E53:E253,"*service*")
D21	D21	=COUNTIF(E53:E253,"*shower*")
D22	D22	=COUNTIF(E53:E253,"*staff*")
D23	D23	=COUNTIF(E53:E253,"*toiletries*")
D24	D24	=COUNTIF(E53:E253,"*traders*")
D25,D35	D25	=SUM(F25:G25)
D26	D26	=COUNTIF(E53:E253,"*welcome*")
D27	D27	=COUNTIF(E53:E253,"*drinks*")
D28	D28	=COUNTIF(E53:E253,"*concierge*")
D29	D29	=COUNTIF(E53:E253,"*parking*")
D30	D30	=COUNTIF(E53:E253,"*Bank*")
D31	D31	=COUNTIF(E53:E253,"*reception*")
D32	D32	=COUNTIF(E53:E253,"*reservation*")
D33	D33	=COUNTIF(E53:E253,"*RS*")
D34	D34	=COUNTIF(E53:E253,"*housekeeping*")
D36	D36	=COUNTIF(E53:E253,"* bath *")

 

[Joe4]

If you are looking for EXACT matches only, simply remove the asterisks from your COUNTIF formulas, i.e. use

=COUNTIF(E53:E253,"tv")

instead of:

=COUNTIF(E53:E253,"*tv*")

 

[russelgreen]

If you are looking for EXACT matches only, simply remove the asterisks from your COUNTIF formulas, i.e. use

=COUNTIF(E53:E253,"tv")

instead of:

=COUNTIF(E53:E253,"*tv*")

Thanks Joe,

will this only count one from each cell? So
For example if food was mentioned twice in one cell would it not count it twice? And also the one that looks for “room service” would also add a number to “room” and “service”?

 

[Joe4]

For example if food was mentioned twice in one cell would it not count it twice? And also the one that looks for “room service” would also add a number to “room” and “service”?

I have already asked twice now for sample data to help make that determination, but you still have not posted it yet.
I thought what you posted above was your sample data.

It is important to post data that is actually representative of the data you are working with.
Otherwise, you may get answers that actually answer the question that you asked, but may not work for your actual data.