Count cells for specific combination of words and not singular words

JABWootton

Board Regular
Joined
Dec 16, 2004
Messages
94
Office Version
  1. 2019
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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
AB
1Cat2
2Cat & Dog
3Dog and cow & cat
Check words
Cell Formulas
RangeFormula
B1B1=COUNT(SEARCH(" cat "," "&A1:A3&" ")+SEARCH(" dog "," "&A1:A3&" "))
 

JABWootton

Board Regular
Joined
Dec 16, 2004
Messages
94
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Details Updated
Thanks. (y)

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

21 02 22.xlsm
AB
1The Cat & Dog went to the Dog & Duck for & Drink & Meal1
2The Cat & Dog went to the Dog & Duck for & Drink & Meal with another Cat & Dog2
3No Cats and Dogs here0
4The cat & dog went to the Dog & Duck for & Drink & Meal0
Count
Cell Formulas
RangeFormula
B1:B4B1=(LEN(A1)+2-LEN(SUBSTITUTE(" "&A1&" "," Cat & Dog ","")))/11
 

JABWootton

Board Regular
Joined
Dec 16, 2004
Messages
94
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Thanks
 

BobColorado

New Member
Joined
Jun 19, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

1614896782095.png

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

Well-known Member
Joined
Jun 29, 2014
Messages
5,973
Office Version
  1. 2016
Platform
  1. Windows
Hi,

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

Book3.xlsx
L
64HP color
65Lexmark color
66Xerox Color
67Xerox Color
68Xerox model 1234 Color
69Color Xerox
70Xerox 37593 model Color
71Color 12345 model Xerox
72Xerow 12345
73
746
Sheet821
Cell Formulas
RangeFormula
L74L74=SUM(COUNTIF(L64:L72,{"*Xerox*Color*","*Color*Xerox*"}))
 

BobColorado

New Member
Joined
Jun 19, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 29, 2014
Messages
5,973
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,808
Messages
5,627,010
Members
416,214
Latest member
boston814

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top