How to count cells with text in Excel using formula
A free Office suite fully compatible with Microsoft Office
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
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:
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:
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.
- 1. How to get month name from a date in Excel (3 easy ways)
- 2. Check if value is in list in Excel (3 easy methods)
- 3. How to Copy File Names in Excel from a Folder?
- 4. How to add text to beginning or end of all cells in Excel
- 5. How to color cell based on value in Excel?
- 6. How to compare two excel sheets and highlight differences