Ravindra BagaleCourses & study guides Track your progress
Medium challenge

Medium: Wildcard COUNTIF over-matching

By · Free ·

Absolute refsNumbers as textLeading spacesFilter left onChart range% vs decimalDuplicate headerROUND vs displayFreeze panesPaste valuesVLOOKUP & textVLOOKUP col indexHLOOKUP TRUESUMIF spacesCOUNTIFS ORIF nest orderDate serialINDEX MATCHWildcard COUNTIFMixed currencyCircular & datesCircular interest3D sum renameNamed range staleCF vs IFPivot staleHard-coded NPVValidation movedPQ step orderTyped-over formulaSUM wrong anchorsFiscal year Apr–MarFind all bugsInterview oral

COUNTIF(…,"AB-10*") returns 4 (includes AB-100, AB-10A). Exact "AB-10" returns 2 (LibreOffice verified).

About this data: Made-up SKUs for Ankit Verma (Kanpur) and Julia Costa (Lisbon). Practice only.
Download broken workbook Download fixed (after you try)

Why → What → How

Why: Wildcards are powerful and over-broad. Inventory counts inflate quietly.

What: Six SKUs. Broken 4 → fixed 2.

How:

  1. Open SKUs.
  2. List what AB-10* matches.
  3. Change criteria to exact AB-10.
  4. Confirm 2.

Ravindra Bagale’s Tip

Use wildcards only when you mean a family of codes — and write a second exact check.

Bugs explained (with mini examples)

Bug 1 — Trailing *

AB-10* ⊃ AB-100, AB-10A.

Bug 2 — No spot-check of matched rows

Always list matches.

Practice tasks (12)

  1. Task 1. Broken count?
    Hint: 4.
  2. Task 2. Fixed?
    Hint: 2.
  3. Task 3. Which extras matched?
    Hint: AB-100, AB-10A.
  4. Task 4. Exact formula.
    Hint: COUNTIF … "AB-10"
  5. Task 5. Interview sentence.
    Hint: “Wildcard over-counted SKUs.”
  6. Task 6. Count AB-10A exact.
    Hint: 1.
  7. Task 7. When is * useful?
    Hint: Prefix families.
  8. Task 8. Escape literal * in criteria?
    Hint: Advanced.
  9. Task 9. Pivot by SKU.
    Hint: Cross-check.
  10. Task 10. Bonus: COUNTIFS with product.
    Hint: Optional.
  11. Task 11. Document exact policy.
    Hint: A14.
  12. Task 12. Save clean file.
    Hint: Yes.

In-browser formula check

Type the fixed formula you would put in the named cell. We check formula text — open the workbook for full recalculation.

Step-by-step solution (try first)
  1. Drop the wildcard.
  2. Count 2. LO: 4 → 2.

Quick quiz

Q1. AB-10* also matches:
Q2. Fixed exact count:
Q3. Broken LO count:
Q4. * in COUNTIF means:
Q5. Exact criteria for AB-10: