What is the formula to count cells with text?
Counting is one of the most common tasks people do in Excel. It’s one of the metric that is often used to summarize the data. For example, count sales done by Bob, or sales more than 500K or quantity of Product X sold. Show Excel has a variety of count functions, and in most cases, these inbuilt Excel functions would suffice. Below are the count functions in Excel:
There may sometimes be situations where you need to create a combination of functions to get the counting done in Excel. One such case is to count cells that contain text strings. This Tutorial Covers:
Count Cells that Contain Text in ExcelText values can come in many forms. It could be:
Have a look at the data set shown below: It has all the combinations of text, numbers, blank, special characters, and logical values. To count cells that contain text values, we will use the wildcard characters:
See Also: Examples of using Wildcard Characters in Excel. Now let’s create formulas to count different combinations. Count Cells that Contain Text in Excel (including Blanks)Here is the formula: =COUNTIF(A1:A11,”*”) This formula uses COUNTIF function with a wildcard character in the criteria. Since asterisk (*) represents any number of characters, it counts all the cells that have text characters in it. It even counts cells that have an empty string in it (an empty string can be a result of formula returning =”” or a cell that contains an apostrophe). While a cell with empty string looks blank, it is counted by this formula. Logical Values are not counted. Count Cells that Contain Text in Excel (excluding Blanks)Here is the formula: =COUNTIF(A1:A11,”?*”) In this formula, the criteria argument is made up of a combination of two wildcard characters (question mark and asterisk). This means that there should, at least, be one character in the cell. This formula does not count cells that contain an empty string (an apostrophe or =””). Since an empty string has no character in it, it fails the criteria and is not counted. Logical Values are also not counted. Count Cells that Contain Text (excluding Blanks, including Logical Values)Here is the formula: =COUNTIF(A1:A11,”?*”) + SUMPRODUCT(–(ISLOGICAL(A1:A11)) The first part of the formula uses a combination of wildcard characters (* and ?). This returns the number of cells that have at least one text character in it (counts text and special characters, but does not count cells with empty strings). The second part of the formula checks for logical values. Excel ISLOGICAL function returns TRUE if there is a logical value and FALSE if there isn’t. A double negative sign ensures that TRUEs are converted into 1 and FALSEs into 0. Excel SUMPRODUCT function then simply returns the number of cells that have a logical value in it. These above examples demonstrate how to use a combination of formulas and wildcard characters to count cells. In a similar fashion, you can also construct formulas to find the SUM or AVERAGE of a range of cells based on the data type in it. Can you use Countif with text?In the empty cell, type: “ =COUNTIF (range, criteria) .” This formula counts the number of cells with text in them from within your specified cell range. For the “range” part, type the cell range you want.
What is the formula to count text in Excel?Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers. For example, you can enter the following formula to count the numbers in the range A1:A20: =COUNT(A1:A20). In this example, if five of the cells in the range contain numbers, the result is 5.
|