Excel: How to Use COUNTIF Partial Text Match


You can use the following formula in Excel to count the number of cells in a range that contain a partial text match:

=COUNTIF(B2:B13, "*Gua*")

This particular formula counts the number of cells in the range B2:B13 that contain the partial text “Gua” anywhere in the cell.

Note: The * symbol is used as a wildcard operator and tells Excel that any characters can come before or after “Gua” in the cell.

The following example shows how to use this formula in practice.

Example: How to Use COUNTIF Partial Text Match in Excel

Suppose we have the following dataset that contains information about various basketball players:

Suppose we would like to count the total number of cells in the Position column that contain the partial text “Gua” anywhere in the cell.

We can type the following formula into cell D2 to do so:

=COUNTIF(B2:B13, "*Gua*")

The following screenshot shows how to use this formula in practice:

Excel COUNTIF partial text match

From the output we can see that there are 6 cells that contain the partial text “Gua” in the Position column.

We can verify this is correct by manually identifying each of the 6 cells that contain “Gua” somewhere in the cell:

Each of the highlighted cells contains “Gua” in the position name.

Note: The COUNTIF function is not case sensitive. If we instead used “gua” in the formula, we would still receive 6 as the output.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

Excel: Use COUNTIF with Multiple Ranges
Excel: Use COUNTIF with Multiple Criteria in Same Column
Excel: COUNTIF Greater Than But Less Than Some Number

Leave a Reply

Your email address will not be published. Required fields are marked *