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
 

BobColorado

New Member
Joined
Jun 19, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
I am currently on an older version of Excel 2016 and not 365 which I plan to move to soon?
thank you for detailing the formulas add the two parts to get the current desired result.
I will take a look at F9 or Evaluate Formula soon.
At first when I failed to get results with your formula, I carefully reviewed each character to find out I was not typing exactly what you shared, but as you say my example of Excel's fix to my issue it added the extras?
THANK YOU for you time, talents in adding me with this.

Bob :)
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,908
Office Version
  1. 2016
Platform
  1. Windows
You're welcome.

I'm using 2016 also, now that you got the formula accepted and working by Excel, may be try removing the extra brackets and see if it'll work, again they don't hurt the formula, but I know for a fact it's not needed. (I don't believe it may have anything to do with regional settings, but I can't be sure)
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,908
Office Version
  1. 2016
Platform
  1. Windows
In L75 is the COUNTIF without SUM, it's just manually adding the 2 different counts together:

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
756
Sheet821
Cell Formulas
RangeFormula
L74L74=SUM(COUNTIF(L64:L72,{"*Xerox*Color*","*Color*Xerox*"}))
L75L75=COUNTIF(L64:L72,"*Xerox*Color*")+COUNTIF(L64:L72,"*Color*Xerox*")
 

BobColorado

New Member
Joined
Jun 19, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Oh, that is very nice to have another way to do this.

THANK YOU!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,488
Messages
5,625,069
Members
416,067
Latest member
newb08

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