Criteria

What a criterion is

A criterion is the condition a cell must meet before a function counts it, adds it up or averages it. It is the second half of a pair: a range to look at and a criterion to test every cell in that range against.

COUNTIF takes one pair, COUNTIFS takes up to 127. SUMIF, AVERAGEIF, SUMIFS, AVERAGEIFS, MAXIFS and MINIFS add the range that holds the numbers to work on.

Mind the order of the arguments:

• SUMIF and AVERAGEIF: the range to test comes first, then the criterion, then the numbers (which may be left out when the tested range holds the numbers)
• SUMIFS, AVERAGEIFS, MAXIFS and MINIFS: the numbers come first and the pairs follow

A criterion is a value, not a formula: it is either a number, a piece of text, or a text that starts with a comparison operator.

The forms a criterion can take

Which cells qualify depends on how the criterion is written. Without an operator the cell must equal the criterion. With a comparison operator in front, Excel compares each cell with the part behind the operator. The table lists the forms you need in an audit; a pattern with * or ? is explained on the wildcards page.

=COUNTIF(B2:B100, ">1000")

=COUNTIF(B2:B100; ">1000")

=COUNTIFS(C2:C100, "MEM", D2:D100, "")

=COUNTIFS(C2:C100; "MEM"; D2:D100; "")

=COUNTIF(D2:D100, "<>")

=COUNTIF(D2:D100; "<>")

Quotes and the ampersand

A comparison operator is part of the criterion, so it goes inside quotes: ">". A cell reference is not text, so it goes outside the quotes and is joined to the operator with &.

Excel glues the two together and then reads the result as one criterion. With the number 5000 in E1, ">" & E1 becomes the text >5000.

The same rule holds for every operator and for functions: ">=" & DATE(2025, 12, 15) joins the operator to a date that DATE calculates. A criterion that is already complete, such as >1000 typed in a cell, can be used by reference alone: COUNTIF(B2:B100, E1).

=COUNTIF(B2:B100, ">" & E1)

=COUNTIF(B2:B100; ">" & E1)

=COUNTIF(B2:B100, ">E1")

=COUNTIF(B2:B100; ">E1")

=COUNTIF(A2:A100, "<>" & E1)

=COUNTIF(A2:A100; "<>" & E1)

Dates in a criterion

Excel stores a date as a serial number, so a date criterion is a number criterion: ">=" & G1 joins the operator to the serial number behind the date in G1. A period needs two criteria on the same date column, one for the start (>=) and one for the end (<=). Both the first and the last day are included, which is what a cut-off test usually needs.

Do not type the date as text. A criterion such as ">=12/15/2025" is converted with the regional setting of the computer, so the same file can give different results on a colleague's laptop. A date cell or DATE(2025, 12, 15) does not depend on the setting.

If the dates carry a time of day (a posting at 31 Dec 2025 14:30), the criterion "<=" & 31 Dec 2025 misses it, because that moment is later than midnight. Use "<" & (end date + 1) instead.

=COUNTIFS(B2:B100, ">=" & G1, B2:B100, "<=" & G2)

=COUNTIFS(B2:B100; ">=" & G1; B2:B100; "<=" & G2)

=COUNTIFS(B2:B100, ">=" & G1, B2:B100, "<" & G2 + 1)

=COUNTIFS(B2:B100; ">=" & G1; B2:B100; "<" & G2 + 1)

Upper case, lower case and numbers stored as text

Criteria ignore the difference between upper and lower case: mem, MEM and Mem all find the same cells. There is no switch to change this. If case matters, you need SUMPRODUCT with EXACT instead of a criterion.

Numbers stored as text (a green triangle in the corner, often after an export) behave differently per kind of criterion:

• an exact criterion such as 5000 finds both the number 5000 and the text 5000
• a comparison such as ">1000" looks at real numbers only, so a text 5000 is not counted
• in the range that SUMIFS adds up, text is ignored, so a column of text amounts gives a total of 0

Convert the column first, for example by multiplying by 1 in a helper column or with Text to Columns.

Several conditions: AND and OR

Pairs in COUNTIFS, SUMIFS, AVERAGEIFS, MAXIFS and MINIFS are always combined with AND: a row counts only when every pair is met.

For OR on one column, add two functions: COUNTIFS(C2:C100, "MEM") + COUNTIFS(C2:C100, "BNK"). With more than two options, put the options in an array constant and wrap the function in SUM: SUM(COUNTIFS(C2:C100, {"MEM","BNK"})). COUNTIFS then returns one count per option and SUM adds them.

This is OR on one column. The options must exclude each other, as two different journal codes do. If one row can meet both options, the row is counted twice.

For OR across different columns (journal is MEM, or the amount is above 10,000) add the two counts and subtract the count of rows that meet both, or use SUMPRODUCT.

Ranges that must match in size

In COUNTIFS, SUMIFS, AVERAGEIFS, MAXIFS and MINIFS every range must have the same number of rows and columns, or Excel returns #VALUE!. B2:B100 with C2:C101 is already too much.

SUMIF and AVERAGEIF are less strict and therefore more dangerous: they use the upper left cell of the range to add up and take the shape of the tested range, so a range that starts one row too low does not give an error but adds the wrong rows.

Fix the ranges with absolute references ($B$2:$B$100) when you copy the formula, and give all ranges the same first and last row. A named range or a table column keeps them equal automatically.

Related topics

• WildcardsWildcards are characters that stand in for other characters, so a criterion or lookup value can match part of a text instead of the whole text.
• OperatorsOperators are the symbols in a formula that calculate, compare, join text or combine references. Excel applies them in a fixed order, which explains many results that look wrong.
• Dates and times as serial numbersExcel stores a date as a count of days and a time as a fraction of a day, so you can subtract, add and compare them. Learn how to count days, repair text dates and add up hours.
• Cell references: relative, absolute and mixedA cell reference is the address a formula reads. Dollar signs decide whether it moves when you copy the formula, and getting that right is what lets one formula fill a whole column or table.