# Functions to Count Text Cells in Excel

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”. ## Bibek Sapkota

I'm Bibek | Tech Enthusiast & Lifelong Learner. | Playing on the Web for the Past Few Years as an SEO Specialist and Full-Time Blogger. I'm constantly seeking out new opportunities to learn and grow, and I love sharing my knowledge with others. This is where I started this blog! Here, you will find me sharing comprehensive reviews, helpful guides, tips-tricks and ways to get the full benefits of evolving technology. On this blog, you can also explore Powerful Knowledge, Tips & Resources On Blogging, SEO and Passive income Opportunities.