How to count cells with text in Excel using formula

August 1, 2022
501 Views
0

A free Office suite fully compatible with Microsoft Office

Free Download

When you're working in Excel, there are many ways to group, sort, and filter your data. One common way to do this is by counting cells with text. It's important to note that data aren't formatted when counting cells with text.

When you have an ample amount of data in excel, it can be tough to count cells with text. Sometimes it might take hours to go through rows and columns. If you want to know how to trust cells with text by using Excel formulas, then continue reading this blog post.

Tips that are provided in this article are compatible with versions 2010/2013/2016.


How to count cells with text by using Formula – COUNTIF METHOD

The COUNTIF function with an asterisk in the criteria argument is the best and simplest way to find the number of text containing cells in Excel. The syntax of this formula is

COUNTIF(range, *)

1.In this example, we want to count the number of cells that contain text. We can do that by entering this formula:=COUNTIF(A2:A3,*)

2. Here we see two text entries in the column, so it has returned two as the answer.

Now you know to count cells with text in Excel by using the COUNTIF function.


How to count cells with text in Excel by using Formula – SUMPRODUCT FUNCTION

Combining the SUMPRODUCT and ISTEXT functions yields another method for determining the number of cells that contain text:

SUMPRODUCT(--ISTEXT(range))

1. The SUMPRODUCT function calculates the sum of the products of all corresponding values in two or more ranges. The --ISTEXT function returns TRUE if any value in the range argument is text and FALSE otherwise.

2. You can use the IF function to check for a specific value before using these functions. For example, if you want to know how many cells contain a particular word, you could use the following formula:

=SUMPRODUCT (--ISTEXT(A2:A3))

3. As we can see from the above example, it has returned two as an answer which means there exist two entries there.


How to count cells with specific text by using Excel Formula – COUNTIF function

COUNTIF is a function that can be used to count cells that contains the specified text. For example, if you want to count all of the cells in a range that contain the word “excel,” you can use the following formula:

=COUNTIF (range, excel)

From the above example, we can see that the word excel occurs only one time, so it has given us 1 in return.

Now you know how to count cells with specific text by using the COUNTIF function.

Did you learn about how to Count Cells With Text In Excel? You can follow WPS academy to learn more features of Word documents, Excel Spreadsheets, and PowerPoint slides.

You can also download WPS Office to edit word documents, Excel Spreadsheets, and PowerPoint free of cost. Download Now! And get an enjoyable working space.