Excel track · Exercise 53 of 86 Paid

Wildcards in text criteria

Write a wildcard criterion in COUNTIF and reuse the same pattern in SUMIF.

Intermediate

Regions and amounts beside codes that share one pattern

Read-only data · screen 1 of 2 · answer in D2

In D2, count the North rows with a wildcard criterion.

	A	B	C	D
1	Code	Region	Amount	Answer
2	AN-1042	North	120
3	BO-2299	South	80
4	CY-3140	North	200
5	DE-4471	North	60

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

In D2, add the amounts of every code that fits the two-letter pattern with a wildcard.

	A	B	C	D
1	Code	Region	Amount	Answer
2	AN-1042	North	120
3	BO-2299	South	80
4	CY-3140	North	200
5	DE-4471	North	60

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

Reference answer

What it does

A criterion is the text that tells COUNTIF or SUMIF which rows count, and two marks inside it act as wildcards. A question mark stands for exactly one character, a star for any run of them including none. Quoted, they turn a plain criterion into a pattern.

On this sheet the region column holds whole words, so Nor* counts the three North rows and N* counts the same three. The code column is where patterns do real work. Every code is two letters, a hyphen and four digits, so ??-* matches all four, and the amounts come to 460.

The criterion always travels in quotes. Without them Excel reads Nor as a name and no count comes back. COUNTIF and SUMIF read criteria the same way, so one pattern serves both.

Syntax

In a criterion, ? matches one character and * matches any run of them

Worked example

On Regions and amounts beside codes that share one pattern, =COUNTIF(B2:B5,"Nor*") returns 3.

=COUNTIF(B2:B5,"Nor*")

Common errors

Error codes for ? and * inside a criteria string, and the same pattern driving a count and a sum
Error	What it means and how to fix it
`#VALUE!`	The criterion cannot be read as text or as a comparison, usually because its quotes are unbalanced.
`#REF!`	A range points at rows that no longer exist after a column was deleted.
`Wrong count`	COUNTIF(B2:B5,"N") matches nothing and returns 0.

At work

Counting or totalling the rows whose code or name fits one pattern.