Table of Contents
The COUNTIF function in Excel is a useful tool for performing a partial text match within a range of data. By using this function, you can easily count the number of cells that contain a specific text or a partial match of that text within a given range. This can be done by specifying the criteria for the partial match within the formula. The COUNTIF function is particularly helpful for quickly analyzing large sets of data and identifying the number of occurrences of a certain text or keyword. It is a simple and efficient way to perform a partial text match in Excel.
Excel: 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:
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.
Additional Resources
The following tutorials explain how to perform other common operations in Excel: