Skip to content

Stockertim Dictionary · Technology

Criteria range

Also called criteria ranges, criteria_range, condition range

In short

The range of cells that a conditional function such as SUMIFS or COUNTIFS checks against a condition to decide which rows to include in its result.

  • Intermediate
  • Concept
  • Checked

In detail

Conditional functions work in pairs of arguments: a criteria range and the criteria applied to it. In =SUMIFS(D2:D500,B2:B500,"North",C2:C500,">=300") the sum range is D2:D500, the first criteria range is B2:B500 with the condition North, and the second is C2:C500 with the condition 300 or more. Only rows that meet every condition contribute to the total. COUNTIFS uses the same pairs without a sum range.

Conditions are written as text when they contain an operator, so ">=300" sits in quotation marks. To read a threshold from a cell instead of typing it, the operator is joined to the reference with an ampersand, as in ">="&F1. Conditions on text ignore case, and they accept wildcard characters for partial matches.

The ranges must line up. Each criteria range has to be the same size and shape as the sum range, starting on the same row, or SUMIFS returns a #VALUE! error. A subtler fault is ranges of equal size that start on different rows, which compares each amount with the label from the wrong row and returns a wrong total with no warning.

Multiple pairs combine with AND logic. To total rows that meet one condition or another, two SUMIFS are added together, taking care that no row meets both. The older single-condition SUMIF puts its arguments in a different order, with the sum range last, which catches people out when they switch between the two.

Worked example

A distributor in Kano wants sales in the North region of at least 300. Column B holds region, C holds amount and D holds the amount to total.

  1. Formula: =SUMIFS(D2:D7,B2:B7,"North",C2:C7,">=300")
  2. The North rows with 300 or more hold 300, 450 and 600
  3. Total: 300 + 450 + 600 = 1,350

Rows that fail either condition are left out, giving 1,350.

In practice

Finance and operations teams use criteria ranges to total spending by cost centre and month, count open tickets by team, or fill the actual column of a budget report from a list of transactions.

Watch out

  • Criteria ranges must match the sum range in size and starting row.
  • A condition with an operator must be in quotation marks, or joined to a cell with an ampersand.

Learn it properly

Official sources

Facts last checked on 9 November 2026.

Education, not advice: the Stockertim Dictionary explains what terms mean. It never recommends buying or selling anything.

English