You can use the following formulas in Excel to use wildcard characters with the COUNTIF function:
Formula 1: COUNTIF with One Wildcard
=COUNTIF(A2:A11, "*string*")
This particular formula counts the number of cells in the range A2:A11 that contain “string” anywhere in the cell.
Formula 2: COUNTIFS with Multiple Wildcards
=COUNTIFS(A2:A11, "*string1*", B2:B11, "*string2*")
This particular formula counts the number of cells where A2:A11 contains “string1” and where B2:B11 contains “string2.”
The following examples show how to use each formula in practice.
Example 1: COUNTIF with One Wildcard
We can use the following formula to count the number of cells in column A that contain “avs” anywhere in the cell:
=COUNTIF(A2:A11, "*avs*")
The following screenshot shows how to use this formula in practice:
From the output we can see that there are 6 cells in column A that contain “avs” in the team name.
Example 2: COUNTIFS with Multiple Wildcards
We can use the following formula to count the number of rows where A2:A11 contains “avs” and where B2:B11 contains “gua”:
=COUNTIFS(A2:A11, "*avs*", B2:B11, "*gua*")
The following screenshot shows how to use this formula in practice:
From the output we can see that there are 4 rows where the team contains “avs” and the position contains “gua” somewhere in the cell.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to COUNTIF Not Equal to Text
Excel: How to COUNTIF Greater Than Zero
Excel: How to Use COUNTIF with Multiple Criteria in Same Column
May I seek your help. I need a solution using CountIf, with wild card and Right Function. Trigger Cell B1= 2023
CountIf(A1:A5,Right(B1,2))=0 ??
(counting number of 23’s, ie 2)
List Require result
09-24 0
01-23 1
01-23 1
04-24 0
11-26 0
Certainly! You can achieve this using the `COUNTIF` function with a wildcard and the `RIGHT` function in Excel. Here’s how you can set it up:
1. **Cell B1**: Contains the trigger value, e.g., `2023`.
2. **Column A**: Contains the list of dates you want to check against the last two digits of the year in B1.
3. **Column C**: Will contain the `COUNTIF` formula to count occurrences of the last two digits of the year from B1 in the corresponding cells in column A.
### Step-by-Step Solution:
1. **Extract the Last Two Digits of the Year**: In cell C1, use the `RIGHT` function to extract the last two digits of the year from cell B1:
“`excel
=RIGHT(B1, 2)
“`
2. **COUNTIF with Wildcard**: In cell B2, enter the following formula to count occurrences of the last two digits of the year (from cell C1) in each cell of column A:
“`excel
=COUNTIF(A1, “*” & RIGHT($B$1, 2) & “*”)
“`
3. **Copy the Formula Down**: Copy the formula in cell B2 down to the other cells in column B to apply it to the entire list.
Here is how you can set it up in your Excel sheet:
| **A** | **B** | **C** |
|———–|—————————|———|
| 09-24 | 2023 | 23 |
| 01-23 | =COUNTIF(A1, “*23*”) | 1 |
| 01-23 | =COUNTIF(A2, “*23*”) | 1 |
| 04-24 | =COUNTIF(A3, “*23*”) | 0 |
| 11-26 | =COUNTIF(A4, “*23*”) | 0 |
### Example with Formulas:
1. **Cell B1**: Enter `2023`.
2. **Cell C1**: Enter `=RIGHT(B1, 2)` which will result in `23`.
3. **Cell B2**: Enter `=COUNTIF(A1, “*” & RIGHT($B$1, 2) & “*”)`, which checks if “23” appears in A1.
4. **Drag the formula in B2 down to apply it to the rest of the cells in column B.
So, for your specific example:
– **A1**: `09-24` → `=COUNTIF(A1, “*23*”)` results in `0`
– **A2**: `01-23` → `=COUNTIF(A2, “*23*”)` results in `1`
– **A3**: `01-23` → `=COUNTIF(A3, “*23*”)` results in `1`
– **A4**: `04-24` → `=COUNTIF(A4, “*23*”)` results in `0`
– **A5**: `11-26` → `=COUNTIF(A5, “*23*”)` results in `0`
This way, you can use the `COUNTIF` function along with the `RIGHT` function and wildcards to count occurrences of the last two digits of the year from cell B1 in the list of dates in column A.