How to How to Use COUNTIFS with Wildcards in Excel in Excel - Practical Guide

Learn to combine COUNTIFS with wildcard characters (* and ?) to count cells matching partial text patterns. This advanced technique enables flexible data analysis when exact matches aren't needed, perfect for analyzing product names, email domains, or inconsistent data formats across large datasets.

Why This Matters

Wildcard COUNTIFS is essential for real-world data analysis where entries vary in format or contain partial matches you need to count. It saves hours of manual sorting and enables dynamic reporting without data cleanup.

Prerequisites

•Basic understanding of COUNTIFS function syntax and multiple criteria
•Familiarity with Excel cell references (absolute vs. relative)
•Knowledge of basic wildcard concepts (* for any characters, ? for single character)

Step-by-Step Instructions

Open Your Spreadsheet and Identify Data Range

Open Excel and locate the column containing text you want to match. Note the range (e.g., A2:A100) and identify which characters vary so you know where to place wildcards.

Click Target Cell for Formula

Select an empty cell where you want the result to appear. This is typically below or beside your data range. Type the equals sign (=) to start your formula.

Enter COUNTIFS with Wildcard Criteria

Type the formula: =COUNTIFS(A2:A100,"*text*") to count cells containing "text" anywhere. Use * before and after your search term for partial matching, or at the end only: "text*" for prefix matching.

Add Multiple Criteria with Wildcards

Extend the formula with additional criteria: =COUNTIFS(A2:A100,"*order*",B2:B100,"*pending*") counts rows where column A contains "order" AND column B contains "pending".

Press Enter and Verify Results

Press Enter to execute the formula. Manually spot-check a few results against your data to ensure wildcards are matching correctly, then adjust wildcard placement if needed.

Alternative Methods

Using SUMPRODUCT with Wildcards

SUMPRODUCT combined with wildcard matching provides more flexibility for complex conditions. Use =SUMPRODUCT((LEN(A2:A100)-LEN(SUBSTITUTE(A2:A100,"text","")))/LEN("text")) to count occurrences, though COUNTIFS is simpler for most cases.

Using Multiple COUNTIF Functions

Combine individual COUNTIF functions for each criterion instead of COUNTIFS. This works identically but uses more characters: =COUNTIF(A2:A100,"*text*")+COUNTIF(B2:B100,"*match*") is less efficient than COUNTIFS.

Tips & Tricks

✓Use * to match zero or more characters and ? to match exactly one character; combine them for precise partial matching like "*_???.txt"
✓Enclose wildcard criteria in quotes; Excel requires "*text*" not *text* to recognize it as a literal pattern
✓Test wildcards on small datasets first to confirm they match intended rows before applying to large ranges

Pro Tips

★Use cell references for criteria instead of hardcoding: =COUNTIFS(A2:A100,"*"&C1&"*") lets users enter search terms without editing the formula.
★Combine wildcards with other operators in criteria for power: =COUNTIFS(A2:A100,"*order*",B2:B100,">100") counts partially matching text AND numeric conditions simultaneously.
★Performance tip: Limit your range size when using wildcards on massive datasets; wildcard matching is slower than exact matching.

Troubleshooting

Formula returns 0 when it should return a count

Check wildcard placement and letter case sensitivity. Excel wildcards are case-insensitive, but verify your search term matches the data. Also confirm your range includes all relevant rows (A2:A100 vs A2:A99).

#NAME? error appears in the cell

This indicates missing or mismatched quotes around your criteria. Ensure criteria like "*text*" are enclosed in double quotes. If copying from external sources, retype the quotes as Excel sometimes doesn't recognize pasted quotes.

Wildcard is matching too many or too few cells

Adjust wildcard position: "*text" matches text at the end, "text*" matches at start, "*text*" matches anywhere. Use ? for single-character precision instead of * for multiple characters when needed.

Formula works in one file but not another

Check regional settings (Formulas > Function Library language). Some Excel versions use semicolons (;) instead of commas (,) as separators; use your region's syntax: =NB.SIS(A2:A100;"*texte*").

Related Excel Formulas

COUNTIF SUMPRODUCT MATCH FIND SEARCH

Frequently Asked Questions

Can I use COUNTIFS with wildcards to count cells NOT matching a pattern?

COUNTIFS doesn't natively support NOT logic with wildcards, but you can subtract from total: =COUNTA(A2:A100)-COUNTIFS(A2:A100,"*unwanted*"). Alternatively, use SUMPRODUCT with NOT logic for more control.

What's the difference between * and ? in wildcard matching?

* matches zero or more characters ("*test" finds "test", "retest", "unittest"), while ? matches exactly one character ("te?t" finds "test", "teat", but not "tempt"). Combine them: "*te?t*" matches any variation.

Do COUNTIFS wildcards work with numbers or only text?

Wildcards work primarily with text; for numeric wildcard-like matching, convert numbers to text or use SUMPRODUCT. COUNTIFS treats numbers as exact values, so "1*" doesn't match 100, 101, etc.—use numeric operators like ">100" instead.

Can I use COUNTIFS with wildcards across multiple sheets?

Yes, reference other sheets in your range: =COUNTIFS(Sheet2.A2:A100,"*text*"). Use the sheet name followed by a dot, then your range. This works identically to single-sheet formulas.

Why is my COUNTIFS formula slow with large datasets?

Wildcard matching is computationally expensive. Optimize by limiting your range to necessary rows, avoiding wildcards on every side ("text*" is faster than "*text*"), or using helper columns with exact matches that COUNTIFS can process faster.