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
 

russelgreen

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

as an example...

“loved the hotel and the room. The bathroom was huge and room service food was amazing.”

this would be posted in the cell under positive feedback.
 

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

russelgreen

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

as an example...

“loved the hotel and the room. The bathroom was huge and room service food was amazing.”

this would be posted in the cell under positive feedback.
Another example:

went to the restaurant and the food was great. Some of the best food I’ve tasted in a long time. Food lovers need to book a table fast.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,844
Office Version
  1. 365
Platform
  1. Windows
So then you are NOT looking for exact matches (exact matches mean that the only thing in the cell would be that one word).
So it makes sense why you are using the asterisks in the COUNTIF functions.

Unfortunately, I think that makes what you are trying to do very problematic. I do not see any good way of looking for "room" and not including phrases like "room service".
That is, unless there are some strict parameters around the content of the phrases.

For example, if all the "room" entries were only "room" and "room service", you could get "room service" like you normally would, i.e.
Excel Formula:
=COUNTIF(E52:E253,"*room service*")
and then you could get just the "room" entries by counting them all, and subtracting the "room service" entries, i.e.
Excel Formula:
=COUNTIF(E52:E253,"*room*") - COUNTIF(E52:E253,"*room service*")

That is the best idea I can come up with.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,825
Office Version
  1. 365
Platform
  1. Windows
See if this could work for you.
First sort your words of interest in column A by their length, descending. I have used column B to do that or you could do it manually.
Then try the formula in column D, copied down - after adjusting the column E range if required.
I have shown at the right a few examples of which rows are counted for the word of interest shown in F1:I1

Punctuation often causes difficulties with 'word' searches like this. I have allowed for "." only. If sentences could end with "?" or "!" or they could contain characters like quote marks (eg The "clean" bathroom was awful) then further difficulties could arise & more substitutions needed or some other approach (vba?) perhaps.

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’). For example, with your version, the D3 formula may need to be confirmed with Ctrl+Shift+Enter before being copied down.

russelgreen.xlsm
ABCDEFGHI
1Positve FeedbackRoom serviceRoomServiceBathroom
2Total
3Room Service123went to the restaurant and the food was great. Some of the best food I’ve tasted in a long time. Food lovers need to book a table fast.1
4Housekeeping120loved the hotel and the room. The bathroom was huge and room service food was amazing.11
5Furnishings110I did not like the hotel service1
6Reservation110The room service was good and so was the TV1
7Facilities100The bathroom was huge and room service food was amazing. loved the hotel and the room.111
8Toiletries100
9Breakfast90
10Concierge90
11Reception90
12Bathroom82
13Building80
14Friendly80
15Location80
16Comfort70
17Heating70
18Service71
19Traders70
20Welcome70
21Parking70
22decour60
23Shower60
24Drinks60
25Clean50
26Staff50
27Scent50
28Food43
29Milk40
30Room40
31Bank40
32Bath40
33B&B30
34Bar30
35Bed30
36TV21
Sheet1
Cell Formulas
RangeFormula
B3:B36B3=LEN(A3)
D3:D36D3=COUNT(SEARCH(" "&A3&" "," "&SUBSTITUTE(E$3:E$10,".","")&" "))-SUMPRODUCT(--ISNUMBER(SEARCH(" "&A3&" "," "&A$2:A2&" ")),D$2:D2)
 

russelgreen

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

ADVERTISEMENT

Thanks Peter, This is what I'm trying to do but it's not working and your example is not 100%. For instance in the counting column D30 the result is zero. But in the G column it shows you counted two "room"? Plus if one of the cells has room and room service in it, it counts room service and not room? Am I missing something?
 
Last edited by a moderator:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,825
Office Version
  1. 365
Platform
  1. Windows
Oops, sorry, I posted the wrong sample worksheet. :oops:
However, I now realise the one I meant to post is also not robust so will hold off posting it for now until I see if I can improve it.

In the interim, I have two questions:
  1. Can you please update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version? (Don’t forget to scroll down & ‘Save’)
  2. If a standard worksheet formula is not possible, would a vba solution be acceptable?
 

russelgreen

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

ADVERTISEMENT

Oops, sorry, I posted the wrong sample worksheet. :oops:
However, I now realise the one I meant to post is also not robust so will hold off posting it for now until I see if I can improve it.

In the interim, I have two questions:
  1. Can you please update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version? (Don’t forget to scroll down & ‘Save’)
  2. If a standard worksheet formula is not possible, would a vba solution be acceptable?
Morning

I think I can use what you did if I put room service in brackets... it’s not a lot of work too do.
it just seems that if you post it twice in a cell it doesn’t like it..

move not used VBA in past but if it works I will give it a go.

thanks for your time, stay safe
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,825
Office Version
  1. 365
Platform
  1. Windows
not used VBA in past but if it works I will give it a go
Then you could try this user-defined function. You would still need to have the column A words sorted by length. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown** in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

** Note that with the formula, the range for the words in column A must be at least 2 cells that is why that range in my formula starts at A2. You would need to adjust the column E range to cover your actual data.
The column A values must not have any blank cells in the range.

(What about that Account details update too so we know what version(s) you have and therefore what resources are available to you?)

VBA Code:
Function ReviewCount(rReviews As Range, rWords As Range, sWord As String) As Long
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long
  Dim s As String, t As String
  
  a = Application.Transpose(rReviews.Value)
  b = Application.Transpose(rWords.Value)
  For i = 1 To UBound(b)
    For j = 1 To UBound(a)
      s = " " & Replace(a(j), ".", " ") & " "
      t = " " & Join(Split(s, " " & b(i) & " ", -1, 1)) & " "
      If Len(t) < Len(s) And b(i) = sWord Then ReviewCount = ReviewCount + 1
      a(j) = t
    Next j
  Next i
End Function

russelgreen.xlsm
ABCDEFGHI
1Positve FeedbackRoom serviceRoomServiceBathroom
2Total
3Room Service123went to the restaurant and the food was great. Some of the best food I’ve tasted in a long time. Food lovers need to book a table fast.1
4Housekeeping120loved the hotel and the room. The bathroom was huge and room service food was amazing.11
5Furnishings110I did not like the hotel service1
6Reservation110The room service was good and so was the TV1
7Facilities100The bathroom was huge and room service food was amazing. loved the hotel and the room.111
8Toiletries100
9Breakfast90
10Concierge90
11Reception90
12Bathroom82
13Building80
14Friendly80
15Location80
16Comfort70
17Heating70
18Service71
19Traders70
20Welcome70
21Parking70
22decour60
23Shower60
24Drinks60
25Clean50
26Staff50
27Scent50
28Food43
29Milk40
30Room42
31Bank40
32Bath40
33B&B30
34Bar30
35Bed30
36TV21
Sheet2
Cell Formulas
RangeFormula
B3:B36B3=LEN(A3)
D3:D36D3=ReviewCount(E$3:E$10,A$2:A3,A3)
 

russelgreen

New Member
Joined
Nov 21, 2012
Messages
18
Office Version
  1. 365
Platform
  1. Windows
Thanks Peter, so I've done that but just get #NAME? in the D Column? Updated my settings as well.
 
Last edited by a moderator:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,825
Office Version
  1. 365
Platform
  1. Windows
Firstly, you are not using the forum quote tags correctly. I fixed post 15 and 19 for you but quotes are for posting what has appeared in another post, not what you are currently writing. Post 19 looked like below before I edited it. It says "Peter_SSs said: ..." but in fact it is what you were saying, not me. ;)

1605268108743.png


The #NAME? error has two likely causes.
- You have not used the same spelling of the function name on the worksheet as it is in the code, or
- You have placed the code in the wrong place in the vba window.

So check both spellings carefully and review steps 1 to 3 in my instructions above.

Thanks for updating your profile. (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,113,892
Messages
5,544,893
Members
410,643
Latest member
sng
Top