# How to count if cells contain text

August 1, 2022
1.2K Views
0

A free Office suite fully compatible with Microsoft Office

A free Office suite fully compatible with Microsoft Office

Excel being an innovative software tries to make its user lives easier by providing them with multiple solutions to solve a specific problem. This software not only helps with data analysis, reports and dashboards. But the most beneficial thing about excel is its speed as it plays a beneficial role in every individual's life and makes their work delivery efficient. Moreover, excel makes it easier for its users by providing them with functions, formulas, and shortcut keys respectively. As if you are looking forward to knowing how many a specific name or text has been used in your excel spreadsheet. Here, you will be using the countif function.

The various methods to count if the cell contains texts are given below:

Countif function for counting cells with text or character:

For counting cells that contain any text or character within. So, we are supposed to use a function that consists of the asterisk symbol (*) when using the COUNTIF formula. And here we have considered our data as mentioned below one:

And the function which we will be performing for it is mentioned below:

Insert the given formula for this function.

FORMULA

=COUNTIF(B5:B11,*)

Press Enter key.

If cells contain text using SUMPRODUCT+ISTEXT Function:

Now, excel with its efficient delivery of work makes it convenient for its user to have multiple methods to solve a specific problem and not get troubled or confused by clinging to a single solution. And now the other methods are given below.

Here we are using the mentioned below formula

=SUMPRODUCT(--ISTEXT(B5:B12))

OR

=SUMPRODUCT(ISTEXT(B5:B12)*1)

Press the enter key.

Again you have successfully manage to learn another method to count cells with text.

Countif cells which are containing specific text in excel spreadsheet:

Here we are counting cells that contain the exact match in various cells, so for this function, we should enter full text along with a quotation mark in the second argument of the formula.

Now,

1. Insert the mentioned formula into the cell.

Formula:

=COUNTIF(B5:B12,Milk)

OR

Press the Enter key.

If cells contain same text using COUNTIF Function:

Our data here is given below:

Insert the mentioned formula in cell E6.

Formula:

=COUNTIF(\$B\$5:\$B\$11,”*”&D6&”*”)

Press Enter Key.Use the Fill handle or else drag it from D6:D9.

Congratulations, You have now learned the method to count if cells contain text or part of text or character.

Finally, you get your cells that contain texts or characters through countif, the methods are a bit tricky but I assure you all are simple and easy to remember.