Previous Topic Next Topic

Count all cells containing a particular text

Solved/Closed
chemisecure Posts 2 Registration date Wednesday March 30, 2016 Status Member Last seen March 30, 2016 - Mar 30, 2016 at 12:58 PM
 RayH - Mar 30, 2016 at 10:01 PM
I am using Microsoft Office 2016, and I am trying to make an autimated numerical count of all cells which contains a certain phrase for a table. An example is "Public Health"; I want to have something similar to the following:

=COUNT(CONTAIN(D:D,"Public Health))

Simply setting an equivalence to "Public Health" will not suffice, as while some of the cells contain only the phrase, still other cells contain something similar to "Energy, Public Health". How would I go about doing this in Excel 2016?

1 response

Answer 1 / 1
RayH
Mar 30, 2016 at 04:07 PM
Hi Chemisecure
Give this a try: 

=COUNT(FIND("Public Health",D:D,1))

Be sure to enter this with Ctrl-Shift-Enter.

It will look like this afterwards: 
={COUNT(FIND("Public Health",D:D,1))}
chemisecure Posts 2 Registration date Wednesday March 30, 2016 Status Member Last seen March 30, 2016
Mar 30, 2016 at 04:55 PM
This worked, thank you!

Also, minor edit to your reply, it's display (at least in Excel 2016) after entering with Ctrl-Shift-Enter has the open bracket before the equals sign, not after.
RayH
Mar 30, 2016 at 10:01 PM
Glad it worked. The error was just a typo on my part.

Similar discussions

help me to add particular cells
mathewmunna -
mathew munna -

2 responses
Counting cells containing only numbers
jutulu -
jutulu -

4 responses
Condition formatting on changing cell color based on another cel
Saitoh7 -
Mazzaropi -

1 response
If a cell contains any text then add a value
cbrizz -
Pharmanumbers -

20 responses
Fill adjacent cell with number from text answer
MitchP -
vcoolio -

20 responses