Excel Criteria and Wildcards

One set of criteria patterns works in every conditional function — SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, MAXIFS and MINIFS. The examples below use SUMIF; swap in the function you need.

How to apply criteria

The criteria argument takes one of two forms — a fixed value you type in, or a reference to a cell.

1 A literal value

=SUMIF(A2:A10, ">100", B2:B10)

Wrap the whole condition — operator and value — in double quotes.

2 A cell reference

=SUMIF(A2:A10, ">"&D2, B2:B10)

Quote just the operator, then join the cell with & — no quotes around the cell.

Criteria patterns

Grouped by what you’re matching — text, numbers, blanks, wildcards, cell values, and dates.

Exact matches

Match a specific text value or number exactly — or everything except it.

	Criteria	What it matches	Formula example
1	"East"	Sums values where the cell equals East.	=SUMIF(A2:A10,"East",B2:B10)
2	100	Sums values where the cell equals 100.	=SUMIF(A2:A10,100,B2:B10)
3	"<>East"	Sums every row except East.	=SUMIF(A2:A10,"<>East",B2:B10)
4	"<>100"	Sums every row except 100.	=SUMIF(A2:A10,"<>100",B2:B10)

Number comparisons

Put the operator and the number together inside double quotes.

	Criteria	What it matches	Formula example
1	">100"	Sums values greater than 100.	=SUMIF(A2:A10,">100",B2:B10)
2	">=100"	Sums values greater than or equal to 100.	=SUMIF(A2:A10,">=100",B2:B10)
3	"<100"	Sums values less than 100.	=SUMIF(A2:A10,"<100",B2:B10)
4	"<=100"	Sums values less than or equal to 100.	=SUMIF(A2:A10,"<=100",B2:B10)

Blank & non-blank

Match cells that are empty, or cells that hold anything at all.

	Criteria	What it matches	Formula example
1	""	Sums values where the cell is blank.	=SUMIF(A2:A10,"",B2:B10)
2	"<>"	Sums values where the cell is not blank.	=SUMIF(A2:A10,"<>",B2:B10)

Wildcards (text patterns)

Use * for any run of characters and ? for exactly one character.

	Criteria	What it matches	Formula example
1	"A*"	Sums values where text starts with A.	=SUMIF(A2:A10,"A*",B2:B10)
2	"*Ltd"	Sums values where text ends with Ltd.	=SUMIF(A2:A10,"*Ltd",B2:B10)
3	"Laptop"	Sums values where text contains Laptop anywhere.	=SUMIF(A2:A10,"Laptop",B2:B10)
4	"<>Laptop"	Excludes rows containing Laptop.	=SUMIF(A2:A10,"<>Laptop",B2:B10)
5	"?"	Matches text containing exactly one character.	=SUMIF(A2:A10,"?",B2:B10)
6	"???"	Matches text containing exactly three characters.	=SUMIF(A2:A10,"???",B2:B10)
7	"?A*"	Matches text where the second character is A.	=SUMIF(A2:A10,"?A*",B2:B10)

Wildcards (* and ?) work with text only.

Compare to a cell value

Quote just the operator, then join the cell reference with &.

	Criteria	What it matches	Formula example
1	">"&D2	Sums values greater than the value in D2.	=SUMIF(A2:A10,">"&D2,B2:B10)
2	"<"&D2	Sums values less than the value in D2.	=SUMIF(A2:A10,"<"&D2,B2:B10)
3	D2	Uses the value in D2 as the condition.	=SUMIF(A2:A10,D2,B2:B10)
4	"<>"&D2	Sums every row except the value in D2.	=SUMIF(A2:A10,"<>"&D2,B2:B10)
5	">="&D2	Sums values greater than or equal to D2.	=SUMIF(A2:A10,">="&D2,B2:B10)
6	"<="&D2	Sums values less than or equal to D2.	=SUMIF(A2:A10,"<="&D2,B2:B10)

Dates

Match today or a specific date; join operators to a date with &.

	Criteria	What it matches	Formula example
1	TODAY()	Sums values for today's date.	=SUMIF(A2:A10,TODAY(),B2:B10)
2	"<"&TODAY()	Sums values before today.	=SUMIF(A2:A10,"<"&TODAY(),B2:B10)
3	">"&TODAY()	Sums values after today.	=SUMIF(A2:A10,">"&TODAY(),B2:B10)
4	DATE(2026,6,25)	Sums values for a specific date.	=SUMIF(A2:A10,DATE(2026,6,25),B2:B10)
5	"<"&DATE(2026,6,25)	Sums values before a given date.	=SUMIF(A2:A10,"<"&DATE(2026,6,25),B2:B10)
6	">"&DATE(2026,6,25)	Sums values after a given date.	=SUMIF(A2:A10,">"&DATE(2026,6,25),B2:B10)