Count cells for specific combination of words and not singular words

[JABWootton]

I am trying to count cells for specific combination of words and not singular words

For example:
In Cell A1 I have the word Cat
In Cell B1 I have the word Cat & Dog

What formula can be used to count the occurrences of the words Cat & Dog in a cell but not just the word Cat?

The answer for the above example would be 1 (Cat & Dog)

Thanks

 

[Peter_SSs]

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

Is this what you want?

21 02 21.xlsm
	A	B
1	Cat	2
2	Cat & Dog
3	Dog and cow & cat
Check words

Cell Formulas
Range	Formula
B1	B1	=COUNT(SEARCH(" cat "," "&A1:A3&" ")+SEARCH(" dog "," "&A1:A3&" "))

 

[JABWootton]

Peter

Thanks for the reply

Details Updated

I just need it to count if the actual combined Words of Cat & Dog are in the cell

Example:

I have in cell A1 the following:

The Cat & Dog went to the Dog & Duck for & Drink & Meal

I just need to count how many times Cat & Dog appear in the cell

So in the above case the result is 1

However, if I have:

The Cat & Dog went to the Dog & Duck for & Drink & Meal with another Cat & Dog

The above answer would be 2

I need to ignore any other words that are in the cell and count only Cat & Dog

Hope this is clearer?

 

[Peter_SSs]

Details Updated

Thanks.

Try this. Note that at the moment this is case-sensitive - see row 4. That can be adjusted if required.

21 02 22.xlsm
	A	B
1	The Cat & Dog went to the Dog & Duck for & Drink & Meal	1
2	The Cat & Dog went to the Dog & Duck for & Drink & Meal with another Cat & Dog	2
3	No Cats and Dogs here	0
4	The cat & dog went to the Dog & Duck for & Drink & Meal	0
Count

Cell Formulas
Range	Formula
B1:B4	B1	=(LEN(A1)+2-LEN(SUBSTITUTE(" "&A1&" "," Cat & Dog ","")))/11

 

[JABWootton]

Thanks

 

[Peter_SSs]

Thanks

You're welcome. Thanks for the follow-up.

 

[BobColorado]

Column L row 64-72
My formula will count the selected words Xerox and Color, next to each other or color to the right, but cant figure out how formula to count if Color is to the left of Xerox and exclude the others?
=countif(L64:L72,"*xerox*color*") Not case sensitive is very OK this document, not sure yet how to do that yet, another time maybe.
The above formula counted row 66, 67, 68, and 70.
I have tried (guessing) using tilde ~, ?, and others.
Thank you for any direction.

 

[jtakw]

Hi,

You probably should have started a new thread.
Try this:

Book3.xlsx
	L
64	HP color
65	Lexmark color
66	Xerox Color
67	Xerox Color
68	Xerox model 1234 Color
69	Color Xerox
70	Xerox 37593 model Color
71	Color 12345 model Xerox
72	Xerow 12345
73
74	6
Sheet821

Cell Formulas
Range	Formula
L74	L74	=SUM(COUNTIF(L64:L72,{"*Xerox*Color*","*Color*Xerox*"}))

 

[BobColorado]

Sorry for not being able start a new thread, still new to using this wonderful forum and wonderful help, thank you.

I tried your formula and it did not work right away. I tried many iterations and finally Excel displayed a box to accept fixing the formula and below is what it did and achieved the result.
=SUM(COUNTIF(L64:L72,({"*Xerox*Color*","*Color*Xerox*"})))
Looks like this version added ( Open left parenthesis before your left Open Brace then added a third right Closed parenthesis at the end of the formula.
Why would it need =Sum first or at all? before the countif? What does the { } Open and Closed brace brackets do for the formula? Why does it need multiple Close right parenthesis?
Thank you for the formula help and hope to understand better is you have the time to detail my questions.

Bob

 

[jtakw]

No apology needed, I suggested that you probably should start a new thread because you'll likely get more responses from different contributors.

As you can see in my sample in Post #8, the formula does not require the extra brackets/parenthesis, and I don't know why your Excel won't accept the formula without them. (It doesn't hurt, but it doesn't need them)
The SUM function is required in order to produce the correct results, we're using COUNTIF to count 2 variations of the words "Xerox" and "Color", in this order, or reversed, with wildcards in front, middle, and end. These 2 different conditions are housed within the curly braces { } as an OR operator, it's telling COUNTIF to count This OR That. The COUNTIF actually produced 2 results (4, and 2 for this sample), the SUM adds them together to give the total of 6.
If you select the COUNTIF portion of the formula in the formula bar, then hit F9, you'll see what I mean, or you can use "Evaluate Formula" in "Formulas" ribbon to go thru the formula.