You can use the following formula in Excel to count the number of cells in a range that contain a partial text match:
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:
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.
Note: The COUNTIF function is not case sensitive. If we instead used “gua” in the formula, we would still receive 6 as the output.
The following tutorials explain how to perform other common operations in Excel: