Count certain words in a cell, even if they appear more than once

[Wad Mabbit]

Hi, I'd like to be able to count the number of certain words in a cell, e.g. in E4 I have Lamb, rice, coffee, coffee.
In L4 (Beverages column) I want to know how many drinks have been had of either: beverage, tea, coffee ovaltine

In L4 I'm using:
=IF(COUNTBLANK([@[Food taken]])>0,"",
IF(ISNUMBER(FIND("beverage",[@[Food taken]])),1,0)+
IF(ISNUMBER(FIND("tea",[@[Food taken]])),1,0)+
IF(ISNUMBER(FIND("coffee",[@[Food taken]])),1,0)+
IF(ISNUMBER(FIND("milo",[@[Food taken]])),1,0)+
IF(ISNUMBER(FIND("ovaltine",[@[Food taken]])),1,0)
)

It does work as a count, but if I have 2 coffees, it doesn't count them as two, but one.
For instance, if, in E4, I have:
Lamb, rice, coffee coffee milo
And L4 results in 2.

I had 2 coffees, and one milo, so I want a read of 3

Screen shot reads
Lamb, rice, coffee coffee, not Lamb, rice, coffee coffee milo

So, anyone got a fix?

Note:
I do have a list:

Search
Cereal
Muesli
Confort food
Fish
Dates
Fruit
Meat
Poultry
Sweet
Vegetable
Banana


Category
Cereal
Cereal
Confort food
Fish
Fruit
Fruit
Meat
Poultry
Sweet
Vegetable
Fruit

but the formula to pull from the table seems broken:
(sorry I can't put the table up here, the add-in doesn't work for me)

=IFERROR(INDEX(MyCategory,1/(1/MAX(ISNUMBER(SEARCH(MySearch,[@[Food taken]] & "|" & [@Notes]))*ROW(MyCategory)))),[@[Food taken]])

 

[Wad Mabbit]

It's accepted, sure. I just wanted my cake and eat it too . Thanks for all your help, you've been fantastic and very professional. I prefer to keep away from VBA, especially as Microsoft is locking macros down, so I'll pass on coding..
When I've tested it more, I'll post it at Microsoft Excel | Business Projects so that it's available..

 

[Fluff]

but my Excel 2016 doesn't support TEXTJOIN

No it doesn't but your profile also shows you are using 365 which does have Textjoin.

 

[Peter_SSs]

It is still unclear to me whether 365 is available to you for this task. If it is, then TEXTSPLIT is a new function being rolled out to subscribers. If/when you have it, this is another method.
I have written this one for exact matches, not partial.

22 04 22.xlsm
	A	B	C	D	E	F
1
2	Coffee
3	Tea			Lamb, Rice, Coffee, coffee		2
4	Ovaltine
Sheet2 (2)

Cell Formulas
Range	Formula
F3	F3	=LET(ts,TEXTSPLIT(D3,", "),IFNA(COLUMNS(FILTER(ts,ISNUMBER(MATCH(ts,A2:A4,0)),NA())),0))

 

[Wad Mabbit]

Personally for this i would go to a UDF and ensure that my words were seperated by a comma, this way the word(s) can be split by the comma and counted as two (or more) words together.

i.e. You could count 'Oat Milk' seperate from just 'Milk'

Yeah, you're so right! I just don't like making something open source when most users don't know how to sandbox VBA files. I'd hate to lead them open to a vulnerability. And Microsoft defaulting VBA files to locked. I haven't used UDF much. Can you do a quick stepthrough?

 

[Georgiboy]

No problem, below is the UDF I have just created:

Function CountWords(str As String, lkUp As Range)
    Dim var As Variant, x As Long, y As Long
  
    var = Split(str, ",")
  
    For x = 0 To UBound(var)
        If Application.CountIf(lkUp, Trim(var(x))) > 0 Then
            y = y + 1
        End If
    Next x
    CountWords = y
End Function

Then below is the way i have used the above function on the worksheet:

Book1
	A	B	C	D	E	F	G	H
1	Apple, Pear, Spoon, Fork, Big Spoon, Spoon, Knife, Sharp Knife	< String to look at			Fruit	Cutlery	< Lookup lists
2					Apple	Spoon
3					Pear	Fork
4						Knife
5
6
7					2	4
8
Sheet1

Cell Formulas
Range	Formula
E7	E7	=CountWords(A1,E2:E3)
F7	F7	=CountWords(A1,F2:F4)

The code splits the words based on finding a , between them - it won't count words that are part of another two word string.

Hope this helps

 

[Wad Mabbit]

No problem, below is the UDF I have just created:

Function CountWords(str As String, lkUp As Range)
    Dim var As Variant, x As Long, y As Long
 
    var = Split(str, ",")
 
    For x = 0 To UBound(var)
        If Application.CountIf(lkUp, Trim(var(x))) > 0 Then
            y = y + 1
        End If
    Next x
    CountWords = y
End Function

Then below is the way i have used the above function on the worksheet:

Book1
	A	B	C	D	E	F	G	H
1	Apple, Pear, Spoon, Fork, Big Spoon, Spoon, Knife, Sharp Knife	< String to look at			Fruit	Cutlery	< Lookup lists
2					Apple	Spoon
3					Pear	Fork
4						Knife
5
6
7					2	4
8
Sheet1

Cell Formulas
Range	Formula
E7	E7	=CountWords(A1,E2:E3)
F7	F7	=CountWords(A1,F2:F4)

The code splits the words based on finding a , between them - it won't count words that are part of another two word string.

Hope this helps

Thanks for that, nicely done.

I wonder, do you thinbk the LET function could do this rather than use VBA?

LET function - Microsoft Support

support.microsoft.com

 

[Georgiboy]

Thanks for that, nicely done.

I wonder, do you thinbk the LET function could do this rather than use VBA?

LET function - Microsoft Support

support.microsoft.com

Potentially, I am a code freak so my formulae skills are lacking...

I would probably do what you are doing with a button click and update periodically, I would do this to keep the workbook snappy as workbooks full of formulae & UDF's can slow down somewhat - if you update periodically with VBA then he workbook can contain no formulae, this in turn allows the work on much bigger files. It may not suit your needs but thought I would explain.

 

[Fluff]

I wonder, do you thinbk the LET function could do this rather than use VBA?

What version of Excel are you actually using?
One moment your saying that 2016 doesn't support Textjoin (even though your profile shows you have 365), then you are asking about LET which doesn't exist in 2016 either?

 

[Wad Mabbit]

What version of Excel are you actually using?
One moment your saying that 2016 doesn't support Textjoin (even though your profile shows you have 365), then you are asking about LET which doesn't exist in 2016 either?

I'm creating this for others to use. Whilst my 2016 doesn't support TEXTJOIN and LET, I can release a version that does, for others to use. Where I use non-backward compatible features, I can set up an IFERROR to a workaround or disable that feature.

Thanks for reminding me I have 365 in my profile. I put it there when I had organisational access. Now that I'm retired, I have no budget for it.

 

[Fluff]

In that case I don't see the need for the LET function, you can use the formula I posted.