Excel track · Exercise 20 of 86 Paid
COUNTIF with wildcards
Count partial matches on text instead of listing every exact value that qualifies.
Advanced
Product list with mixed names and unit quantities
Read-only data · screen 1 of 2 · answer in B9
In B9, count the products whose name starts with "Widget" using one wildcard.
| A | B | |
|---|---|---|
| 1 | Product | Qty |
| 2 | Widget A | 12 |
| 3 | Widget B | 8 |
| 4 | Gadget A | 5 |
| 5 | Widget C | 20 |
| 6 | Gizmo B | 3 |
| 7 | Widget D | 9 |
| 8 | Gadget B | 4 |
| 9 | Names starting with Widget |
In B9, count the products whose name ends with "B".
| A | B | |
|---|---|---|
| 1 | Product | Qty |
| 2 | Widget A | 12 |
| 3 | Widget B | 8 |
| 4 | Gadget A | 5 |
| 5 | Widget C | 20 |
| 6 | Gizmo B | 3 |
| 7 | Widget D | 9 |
| 8 | Gadget B | 4 |
| 9 | Ending in B |
Reference answer
What it does
COUNTIF criteria accept two wildcards, and either one turns an exact match into a pattern. The star stands for any run of characters, including none, so Widget* covers Widget A, Widget B, Widget C and Widget D. The question mark stands for exactly one character, so Gadget? misses Gadget B because of the space.
The wildcards sit inside the quotes with the rest of the pattern. Widget* returns 4 here. Move the star to the front and *B returns 3, the three names ending in B: Widget B, Gizmo B and Gadget B.
Without a wildcard the criterion is the whole cell, so Widget on its own returns 0 because no cell holds that word. A star on the wrong end counts another set of rows and still returns a number.
Syntax
=COUNTIF(range, "pattern*") counts the names that match; * stands for any run of characters and ? for exactly oneWorked example
On Product list with mixed names and unit quantities, =COUNTIF(A2:A8,"Widget*") returns 4.
=COUNTIF(A2:A8,"Widget*")Common errors
| Error | What it means and how to fix it |
|---|---|
#NAME? | A pattern typed without quotes, as in Widget*, is read as a name that does not exist. |
Matches nothing | An exact criterion that no cell holds, such as Widget on its own, returns 0. |
Wrong end | A star on the wrong end counts a different set of rows and returns that count. |
At work
Counting every product in one family, and counting records that share a code prefix or suffix.