< Go Back

Count cells that contain specific text

We’d look at how cells with specific text are counted in Excel. This function also makes use of the COUNTIF function like many other functions in Excel for counting purposes.

Formula

=COUNTIF(range,“*text*”)

Arguments

range – refers to the range of cells which the function focuses on when returning values upon the provision of the formula in a cell.

text – this is the particular text that would be in the cell during the “counting” operation.

Explanation

The COUNTIF function here works by doing a count of the total number of cells in a particular range. It does this by matching the character in each cell against a particular pattern.

For instance, if we have a formula as = COUNTIF (A2:A10, “*n*”). COUNTIF matches the content in every cell against the “*n*” pattern.

“*” – is a wildcard, which refers to “match any number of characters”.

Example

Step 1: We consider a range of text, from where we use the COUNTIF function to generate cells that contain specific text.

In different rows, we have cells for the named range, criteria for the COUNTIF function to operate, and the result of the function.

Figure 1.  The range of text to be counted by the COUNTIF function.

Step 2: For our example, we have our named range to include the entire column of the range. The criteria for the function are *o* and *r*.

Figure 2. Excel showing the named range and criteria for COUNTIF.

Step 3: The formula: =COUNTIF(A3A8,) returns the number of specific text, “o”, in the named range, which in this case is 3.

Figure 3. COUNTIF counts the number of specific text in the named range.

Notes

The COUNTIF function returns the number of specific text in a named range, regardless of the case the letters are in.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar