Excel Formulas

By | 2017-02-22T20:50:02+00:00 November 30th, 2016|HSE KPI, Main|0 Comments

Count Specific text

Generic formula


To count the number of cells that contain certain text, you can use the COUNTIF function. In the generic form of the formula (above), rng is a range of cells, txt represents the text that cells should contain, and “*” is a wildcard matching any number of characters.

In the example, the active cell contains this formula:


How thisĀ formula works

COUNTIF counts the number of cells in the range that contain “a” by matching the content of each cell against the pattern “*a*”, which is supplied as the criteria. The “*” symbol (the asterisk) is a wildcard in Excel that means “match any number of characters”, so this pattern will count any cell that contains “a” in any position. The count of cells that match this pattern is returned as a number.

You can easily adjust this formula to use the contents of another cell for the criteria. For example, if A1 contains the text you want to match, use the formula:


Case-sensitive version

If you need a case-sensitive version, you can’t use COUNTIF. Instead you can test each cell in the range using a formula based on the FIND function and the ISNUMBER function, as explained here.

FIND is case-sensitive, and you’ll need to give it the range of cells and then use SUMPRODUCT to count the results. The formula looks like this:


Where text is the text you are looking for, and rng is the range of cells you want to count. There’s no need to use wildcards, because FIND will return a number if text is found anywhere in the cell.

About the Author:

HSE Professional with more than 14 years experience in the Upstream Oil & Gas industry. Created this website to help the HSE Professionals from around the globe to form a sharing and learning community. My inspiration in HSE is a verse from Quran: whoever saves one - it is as if he had saved mankind entirely [Quran, al Maida 5:32]