Excel Countif Cell Contains Text

Using Excel Countif to Count Cells Containing Specific Text

When working with Excel, there are times when you need to count the number of cells in a range that contain specific text. This can be achieved using the COUNTIF function, which is a part of Excel’s statistical functions. The COUNTIF function is versatile and can be used in various scenarios, including counting cells that contain specific text, numbers, or dates. In this article, we will explore how to use the COUNTIF function to count cells that contain specific text.

Basic Syntax of COUNTIF

The basic syntax of the COUNTIF function is as follows:

COUNTIF(range, criteria)

Where: - range is the range of cells that you want to count. - criteria is the criteria that you want to use to count the cells. This can be a number, text, or a cell reference.

Counting Cells Containing Specific Text

To count cells that contain specific text, you can use the COUNTIF function with the criteria in the format of "*text*". The asterisks (*) are wildcards that match any sequence of characters. For example, to count the number of cells in the range A1:A10 that contain the text “apple”, you can use the following formula:

=COUNTIF(A1:A10, "*apple*")

This formula will return the number of cells in the range A1:A10 that contain the text “apple”, regardless of the position of the text in the cell.

Counting Cells Containing Multiple Text

If you need to count cells that contain multiple texts, you can use the COUNTIF function with multiple criteria. For example, to count the number of cells in the range A1:A10 that contain either “apple” or “banana”, you can use the following formula:

=COUNTIF(A1:A10, "*apple*") + COUNTIF(A1:A10, "*banana*")

This formula will return the total number of cells in the range A1:A10 that contain either “apple” or “banana”.

Using COUNTIFS for Multiple Criteria

If you need to count cells that meet multiple criteria, you can use the COUNTIFS function, which is an extension of the COUNTIF function. The COUNTIFS function allows you to specify multiple ranges and criteria. For example, to count the number of cells in the range A1:A10 that contain “apple” and are greater than 10, you can use the following formula:

=COUNTIFS(A1:A10, "*apple*", B1:B10, ">10")

This formula will return the number of cells in the range A1:A10 that contain “apple” and have a value greater than 10 in the corresponding cell in the range B1:B10.

Example Use Case

Suppose you have a list of sales data in the range A1:B10, where column A contains the product name and column B contains the sales amount. You want to count the number of sales for products that contain the text “electronic”. You can use the following formula:

=COUNTIF(A1:A10, "*electronic*")

This formula will return the number of sales for products that contain the text “electronic”.

Product	Sales Amount
Electronic Phone	100
Laptop Electronic	200
Tablet	50
Electronic Watch	150

Using the formula above, the result would be 3, indicating that there are 3 sales for products that contain the text “electronic”.

💡 Note: The COUNTIF function is case-sensitive, so if you want to count cells that contain specific text regardless of case, you can use the LOWER or UPPER function to convert the text to lowercase or uppercase before counting.

To summarize, the COUNTIF function is a powerful tool in Excel that allows you to count cells that meet specific criteria, including containing specific text. By using the COUNTIF function with wildcards and multiple criteria, you can perform complex counting tasks with ease. Whether you need to count cells that contain specific text, numbers, or dates, the COUNTIF function is a versatile solution that can help you achieve your goals.