As an Excel user, you may have a need to count the number of cells that contain text in a worksheet. This can be useful for various purposes, such as analyzing data or identifying errors in a dataset. Fortunately, Excel provides several functions that allow you to count text cells based on specific criteria. In this article, we will discuss five functions that can be used for this purpose: COUNTIF, COUNTIFS, SUMPRODUCT, ISTEXT, and NOT.
Problem
Before discussing the functions, let’s first consider the problem at hand. Suppose you have a worksheet containing a list of employees and their respective departments, and you want to count the number of cells that contain the word “Marketing” in the department column. This is a common scenario in data analysis, where you need to extract specific information from a dataset.
COUNTIF Function
The COUNTIF function is a basic Excel function that allows you to count cells based on a single criteria. In our example, you can use the COUNTIF function to count the number of cells that contain the word “Marketing” in the department column. The syntax of the COUNTIF function is as follows:
=COUNTIF(range, criteria)Here, range is the range of cells that you want to count, and criteria is the text string or expression that you want to count. To count the cells that contain the word “Marketing” in the department column, you can use the following formula:
=COUNTIF(B2:B10,"Marketing")Here, B2:B10 is the range of cells that contains the department names, and “Marketing” is the criteria that you want to count.
COUNTIFS Function
The COUNTIFS function is a more advanced version of the COUNTIF function that allows you to count cells based on multiple criteria. In our example, you can use the COUNTIFS function to count the number of cells that contain the word “Marketing” in the department column and the word “Female” in the gender column. The syntax of the COUNTIFS function is as follows:
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],...)Here, criteria_range1 is the first range of cells that you want to apply the first criteria to, criteria1 is the first criteria, and [criteria_range2, criteria2] is an optional second range of cells and criteria that you want to apply. To count the cells that contain the word “Marketing” in the department column and the word “Female” in the gender column, you can use the following formula:
=COUNTIFS(B2:B10,"Marketing",C2:C10,"Female")Here, B2:B10 is the range of cells that contains the department names, C2:C10 is the range of cells that contains the gender information, “Marketing” is the criteria that you want to count in the department column, and “Female” is the criteria that you want to count in the gender column.
SUMPRODUCT Function
The SUMPRODUCT function is another Excel function that can be used to count cells based on multiple criteria. It works by multiplying two or more arrays and then adding up the results. In our example, you can use the SUMPRODUCT function to count the number of cells that contain the word “Marketing” in the department column and the word “Female” in the gender column. The syntax of the SUMPRODUCT function is as follows:
`=SUMPRODUCT((criteria_range1=criteria1)(criteria_range2=criteria2)...)`Here, criteria_range1 is the first range of cells that you want to apply the first criteria to, criteria1 is the first criteria, and [criteria_range2=criteria2] is an optional second range of cells and criteria that you want to apply. To count the cells that contain the word “Marketing” in the department column and the word “Female” in the gender column, you can use the following formula:
=SUMPRODUCT((B2:B10="Marketing")*(C2:C10="Female"))Here, B2:B10 is the range of cells that contains the department names, C2:C10 is the range of cells that contains the gender information, “Marketing” is the criteria that you want to count in the department column, and “Female” is the criteria that you want to count in the gender column.
ISTEXT Function
The ISTEXT function is a logical function that allows you to check if a cell contains text or not. In our example, you can use the ISTEXT function to count the number of cells that contain text in the department column. The syntax of the ISTEXT function is as follows:
=ISTEXT(value)Here, value is the cell or range of cells that you want to check for text. To count the cells that contain text in the department column, you can use the following formula:
=SUMPRODUCT(--ISTEXT(B2:B10))Here, B2:B10 is the range of cells that contains the department names, and — is used to convert the TRUE/FALSE values returned by the ISTEXT function to 1s and 0s that can be summed by the SUMPRODUCT function.
NOT Function
The NOT function is another logical function that allows you to reverse the logic of a condition. In our example, you can use the NOT function to count the number of cells that do not contain the word “Marketing” in the department column. The syntax of the NOT function is as follows:
=NOT(value)Here, value is the condition that you want to reverse. To count the cells that do not contain the word “Marketing” in the department column, you can use the following formula:
=COUNTIF(B2:B10,"<>"&"Marketing")Here, B2:B10 is the range of cells that contains the department names, “<>” means “not equal to”, and “&” is used to concatenate the “not equal to” operator with the criteria “Marketing”.
