Practice free

COUNTIFS function

COUNTIFS counts the rows where all of the listed conditions hold at once, such as orders from one region in a given month. It is the standard way to do AND-style counting.

Example

Read it as a sentence: count the rows where M2:M7 (Region) is North and N2:N7 (Product) is A, which gives 2.

Practice this in the app

Syntax

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Arguments in [square brackets] are optional.

criteria_range1 (required)
First range to test.
criteria1 (required)
Condition for the first range.
criteria_range2 (optional)
Optional additional range.
criteria2 (optional)
Condition for the additional range.

Returns: How many rows meet every condition.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=COUNTIFS(Data!M2:M7,"North",Data!N2:N7,"A") 2 Two criteria
=COUNTIFS(Data!M2:M7,"North",Data!O2:O7,">60") 2 Numeric + text
=COUNTIFS(Data!M2:M7,"West") 0 No match
=COUNTIFS(Data!M2:M7,{"North";"South"},Data!N2:N7,"A") 2 Array criteria spill
=COUNTIFS(Data!M2:M7,"?ast") 1 Wildcard ?
=COUNTIFS(Items!C2:C13,"Tools",Items!I2:I13,FALSE) 3 Site: featured, uninsured tools
=COUNTIFS(Data!M2:M7,"North",Data!N2:N5,"A") #VALUE! Mismatched range sizes
=COUNTIFS(Items!C2:C13,"Tools",Items!I2:I12,FALSE) #VALUE! Site: ranges of different sizes

The Data sheet these formulas read

A cell that starts with = holds that formula.

The Items sheet these formulas read

Watch out for

  • All ranges must be the same size.
  • Conditions combine with AND; for OR add separate COUNTIFS together.
  • Needs real ranges.
  • Excel refuses the formula outright if you type an array constant such as {1,2,3} where a range is expected; point it at cells instead (measured in Excel 16.0.20527).

Where it works

Excel
COUNTIFS is in every version of Excel from 2007 on.
Google Sheets
Same name, same behaviour for everyday use.

Part of Statistical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .