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

 

[Georgiboy]

Perhaps you could try the LEN, SUBSTITUTE option:

=(LEN([@[Food Taken]])-LEN(SUBSTITUTE(UPPER([@[Food Taken]]),UPPER("beverage"),"")))/LEN("beverage")+
(LEN([@[Food Taken]])-LEN(SUBSTITUTE(UPPER([@[Food Taken]]),UPPER("tea"),"")))/LEN("tea")+
(LEN([@[Food Taken]])-LEN(SUBSTITUTE(UPPER([@[Food Taken]]),UPPER("coffee"),"")))/LEN("coffee")+
(LEN([@[Food Taken]])-LEN(SUBSTITUTE(UPPER([@[Food Taken]]),UPPER("milo"),"")))/LEN("milo")+
(LEN([@[Food Taken]])-LEN(SUBSTITUTE(UPPER([@[Food Taken]]),UPPER("ovaltine"),"")))/LEN("ovaltine")

 

[Fluff]

If you have a list for each group, you could also use

+Fluff 1.xlsm
	A	B	C	D	E	F
1
2	Coffee
3	Tea			Lamb, Rice, Coffee, coffee		2
4	Ovaltine
Data

Cell Formulas
Range	Formula
F3	F3	=ROWS(FILTERXML("<k><m>"&SUBSTITUTE(UPPER(D3),", ","</m><m>")&"</m></k>","//m[.='"&TEXTJOIN("' or .='",,UPPER(A2:A4))&"']"))

 

[Wad Mabbit]

If you have a list for each group, you could also use

+Fluff 1.xlsm
	A	B	C	D	E	F
1
2	Coffee
3	Tea			Lamb, Rice, Coffee, coffee		2
4	Ovaltine
Data

Cell Formulas
Range	Formula
F3	F3	=ROWS(FILTERXML("<k><m>"&SUBSTITUTE(UPPER(D3),", ","</m><m>")&"</m></k>","//m[.='"&TEXTJOIN("' or .='",,UPPER(A2:A4))&"']"))

Great idea and very good regards maintenance, Maybe it didn't copy over well, but my Excel 2016 doesn't support TEXTJOIN

 

[Wad Mabbit]

Great idea and very good regards maintenance, Maybe it didn't copy over well, but my Excel 2016 doesn't support TEXTJOIN

PS, I've wrapped it in an IFERROR until I can upgrade to Office 2021. Here's the fruit group

=IF(COUNTBLANK([@[Food taken]])>0,"",
IFERROR(ROWS(FILTERXML("<k><m>"&SUBSTITUTE(UPPER([@[Food taken]]),", ","</m><m>")&"</m></k>","//m[.='"&TEXTJOIN("' or .='",,UPPER(grpFruit))&"']")),

IF(COUNTBLANK([@[Food taken]])>0,"",
(LEN([@[Food taken]])-LEN(SUBSTITUTE(UPPER([@[Food taken]]),UPPER("beverage"),"")))/LEN("fruit")+
(LEN([@[Food taken]])-LEN(SUBSTITUTE(UPPER([@[Food taken]]),UPPER("apricot"),"")))/LEN("apricot")+
(LEN([@[Food taken]])-LEN(SUBSTITUTE(UPPER([@[Food taken]]),UPPER("blackberr"),"")))/LEN("blackberr")+
(LEN([@[Food taken]])-LEN(SUBSTITUTE(UPPER([@[Food taken]]),UPPER("cherr"),"")))/LEN("cherr")+
(LEN([@[Food taken]])-LEN(SUBSTITUTE(UPPER([@[Food taken]]),UPPER("coconut"),"")))/LEN("coconut")+
(LEN([@[Food taken]])-LEN(SUBSTITUTE(UPPER([@[Food taken]]),UPPER("date"),"")))/LEN("date")+
(LEN([@[Food taken]])-LEN(SUBSTITUTE(UPPER([@[Food taken]]),UPPER("lychee"),"")))/LEN("lychee")+
(LEN([@[Food taken]])-LEN(SUBSTITUTE(UPPER([@[Food taken]]),UPPER("mango"),"")))/LEN("mango")+
(LEN([@[Food taken]])-LEN(SUBSTITUTE(UPPER([@[Food taken]]),UPPER("mulberr"),"")))/LEN("mulberr")+
(LEN([@[Food taken]])-LEN(SUBSTITUTE(UPPER([@[Food taken]]),UPPER("nectarine"),"")))/LEN("nectarine")+
(LEN([@[Food taken]])-LEN(SUBSTITUTE(UPPER([@[Food taken]]),UPPER("olive"),"")))/LEN("olive")+
(LEN([@[Food taken]])-LEN(SUBSTITUTE(UPPER([@[Food taken]]),UPPER("peach"),"")))/LEN("peach")+
(LEN([@[Food taken]])-LEN(SUBSTITUTE(UPPER([@[Food taken]]),UPPER("plum"),"")))/LEN("plum")+
(LEN([@[Food taken]])-LEN(SUBSTITUTE(UPPER([@[Food taken]]),UPPER("ovaltine"),"")))/LEN("raspberr")
)
)
)

 

[Georgiboy]

Looks good, watch out for 'beverage' & 'ovaltine' as the second word needs to be the same.

Currently you have:
"beverage"),"")))/LEN("fruit")
"ovaltine"),"")))/LEN("raspberr")

Should be:
"beverage"),"")))/LEN("beverage")
"ovaltine"),"")))/LEN("ovaltine")

 

[Wad Mabbit]

Looks good, watch out for 'beverage' & 'ovaYeah spotted that, pasted from the wrong clipbpoard...

ltine' as the second word needs to be the same.

Currently you have:
"beverage"),"")))/LEN("fruit")
"ovaltine"),"")))/LEN("raspberr")

Should be:
"beverage"),"")))/LEN("beverage")
"ovaltine"),"")))/LEN("ovaltine")

 

[Wad Mabbit]

Thanks for that, I did spot it !

On that note - sorta - Is there a way to look for exact matches in some instances and partial matches in others.

For instance, Milk, and Oat Milk. The former is a FODMAP issue and the latter a galactooligosaccharide. I wouldn't want to trigger a FODMAP inclusion on oat milk. Similarly Pea and Pear.

 

[bebo021999]

If more items in group, and more groups added, VBA is a good choice.
Is it accepted?

 

[Georgiboy]

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'