Count If Issue

russelgreen

New Member
Joined
Nov 21, 2012
Messages
18
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,871
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 21, 2012
Messages
18
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,871
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 21, 2012
Messages
18
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks Joe...

Master Feedback 2020.xlsx
J
11
11 Nov
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,871
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 21, 2012
Messages
18
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
ADEFGHIJK
1Positve Feedback
2Total
3B&B0
4Bar0
5Bathroom0
6Bed0
7Breakfast0
8Building0
9Clean0
10Comfort0
11decour0
12Facilities0
13Heating0
14Food0
15Milk0
16Friendly0
17Furnishings0
18Location0
19Room1
20Service0
21Shower0
22Staff0
23Toiletries0
24Traders0
25TV000
26Welcome0
27Drinks0
28Concierge0
29Parking0
30Bank0
31Reception0
32Reservation0
33Room Service0
34Housekeeping0
35Scent000
36Bath0
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51Possitive Feedback
52NameAreaFeedback
53Room
54
55
56
57
58
59
60
11 Nov
Cell Formulas
RangeFormula
F25F25=COUNTIF(E53:E253,"*tv*")
G25G25=COUNTIF(E53:E253,"*television*")
F35F35=COUNTIF(E53:E253,"*scent*")
G35G35=COUNTIF(E53:E253,"*smell*")
D3D3=COUNTIF(E53:E253,"*B&B*")
D4D4=COUNTIF(E53:E253,"*bar*")
D5D5=COUNTIF(E53:E253,"*"&A5&"*")
D6D6=COUNTIF(E53:E253,"*Bed*")
D7D7=COUNTIF(E53:E253,"*Breakfast*")
D8D8=COUNTIF(E53:E253,"*Building*")
D9D9=COUNTIF(E53:E253,"*clean*")
D10D10=COUNTIF(E53:E253,"*comfort*")
D11D11=COUNTIF(E53:E253,"*decour*")
D12D12=COUNTIF(E53:E253,"*facilities*")
D13D13=COUNTIF(E53:E253,"*heating*")
D14D14=COUNTIF(E53:E253,"*food*")
D15D15=COUNTIF(E53:E253,"*milk*")
D16D16=COUNTIF(E53:E253,"*friendly*")
D17D17=COUNTIF(E53:E253,"*furnishing*")
D18D18=COUNTIF(E53:E253,"*location*")
D19D19=COUNTIF(E53:E253,"*"&A19&"*")
D20D20=COUNTIF(E53:E253,"*service*")
D21D21=COUNTIF(E53:E253,"*shower*")
D22D22=COUNTIF(E53:E253,"*staff*")
D23D23=COUNTIF(E53:E253,"*toiletries*")
D24D24=COUNTIF(E53:E253,"*traders*")
D25,D35D25=SUM(F25:G25)
D26D26=COUNTIF(E53:E253,"*welcome*")
D27D27=COUNTIF(E53:E253,"*drinks*")
D28D28=COUNTIF(E53:E253,"*concierge*")
D29D29=COUNTIF(E53:E253,"*parking*")
D30D30=COUNTIF(E53:E253,"*Bank*")
D31D31=COUNTIF(E53:E253,"*reception*")
D32D32=COUNTIF(E53:E253,"*reservation*")
D33D33=COUNTIF(E53:E253,"*RS*")
D34D34=COUNTIF(E53:E253,"*housekeeping*")
D36D36=COUNTIF(E53:E253,"* bath *")
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,871
Office Version
  1. 365
Platform
  1. Windows
If you are looking for EXACT matches only, simply remove the asterisks from your COUNTIF formulas, i.e. use
Excel Formula:
=COUNTIF(E53:E253,"tv")
instead of:
Excel Formula:
=COUNTIF(E53:E253,"*tv*")
 

russelgreen

New Member
Joined
Nov 21, 2012
Messages
18
Office Version
  1. 365
Platform
  1. Windows
If you are looking for EXACT matches only, simply remove the asterisks from your COUNTIF formulas, i.e. use
Excel Formula:
=COUNTIF(E53:E253,"tv")
instead of:
Excel Formula:
=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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,871
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,207
Messages
5,546,546
Members
410,745
Latest member
citrictango
Top