Using the Excel COUNTIF to Count Cells That Contain Text from other Cells

You can use the COUNTIF function to count a cell that contains text from another cell in Excel.

This is the sample dataset.

1. Using the Asterisk Sign with the COUNTIF Function Including Blank Cells

The Asterisk sign (*) stands for the text, and the COUNTIF function counts the cells.

Steps:

Go to C17 and enter the formula:

=COUNTIF(C5:C15,”*”)

The COUNTIF(C5:C15,”*”) syntax counts the cell containing text. The Asterisk symbol acts as text in the argument.

2. Using the Asterisk Sign with the COUNTIF Function Excluding Blank Cells

Steps:

Go to C17 and enter the formula:

=COUNTIF(C5:C15,”?*”)

The question mark excludes the blank cells and the Asterisk (*) is a text argument. Then the COUNTIF function counts the cells with text values excluding blanks.

3. Counting Cells Using a Cell Reference.

Steps:

Go to C18 and enter the formula:

=COUNTIF(C5:C15,”*”&B18&”*”)

Here,

B18 = Text that you want to search.

C5:C15= Entire data range.

COUNTIF(C5:C15,”*”&B18&”*”) takes the text value in B18 and counts it in C5:C15.

Go to C19 and enter the formula:

=COUNTIF(C5:C15,”*”&B19&”*”)

The COUNTIF(C5:C15,”*”&B19&”*”) takes the text value in B19 and counts it in C5:C15.

This is the output.

4. Utilizing the COUNTIF for a Specific Text

Steps:

Go to C18 and enter the formula:

=COUNTIF(C5:C15, “Red”)

The COUNTIF counts the specific text “Red” in C5:C15.

Go to C19 and enter the formula:

=COUNTIF(C5:C15, “Blue”)

The COUNTIF counts the specific text “Blue” in C5:C15.

Using the COUNTIF Function to Count Cells That Contain Partial Text from Another Cell in Excel

Steps:

Go to C17 and enter the formula:

=COUNTIF(C5:C15, “R*”)

The COUNTIF counts text starting with “R” in C5:C15.

Note: The asterisk (*) after the partial text counts text starting with the alphabet. Before the partial text, it returns text that ends with the inserted argument.

Applying the COUNTIF Function to Count Cells That Contain Numbers from Another Cell in Excel

Steps:

Go to C17 and enter the formula:

=COUNTIF(C5:C15,”>0″)

The COUNTIF(C5:C15,”>0″) counts numbers greater than zero. It doesn’t count the blanks and negative values.

Utilizing the COUNTIF Function to Count Cells That Contain Text from a List

Steps:

Go to C5 and enter the formula:

=IF(OR(COUNTIF(B5,”*”&$E$5:$E$7&”*”)), “Yes”, “No”)

$E$5:$E$7 = The cells to be checked.

Formula Breakdown:

COUNTIF(B5,”*”&$E$5:$E$7&”*”)→ takes the text Apple, Pears, and Cherry as input and checks for a match in B5.

OR(COUNTIF(B5,”*”&$E$5:$E$7&”*”))→ The OR function returns the result of the COUNTIF function into the logical arguments. B5 has Apple on its list. Then, it will return 1 as it is considered a TRUE value. It returns 0 when text doesn’t match the item.

IF(OR(COUNTIF(B5,”*”&$E$5:$E$7&”*”)), “Yes”, “No”)→ The IF function shows the result Yes or No for the logical TRUE and FALSE. If the logical argument from the OR function shows 1, the IF function, considers it TRUE and returns Yes. 0 is shown for Boolean logic.