COUNTIFS Generator
Count the rows that meet two or more conditions at the same time.
COUNTIFS
Count the rows that meet two or more conditions at the same time.
Column checked against the first condition. There is no separate count range.
Condition for the first range. Quote text and comparisons; leave cell references unquoted.
Column checked against the second condition. Must be the same size as the first range.
Condition for the second range, for example ">100", "<>" or "*paid*".
=COUNTIFS(A2:A100, "West", B2:B100, ">100")Worked Example
An order list with region in column A and units in column B; you want to know how many West orders exceeded 100 units.
=COUNTIFS(A2:A100, "West", B2:B100, ">100")Returns: Returns the number of rows where column A is West AND column B is greater than 100 - so 4 if exactly four rows meet both tests.
Checks Before You Paste
- •COUNTIFS keeps COUNTIF's ordering, range first then criteria, so arguments alternate: range1, criteria1, range2, criteria2. There is no separate count range - COUNTIFS returns the number of rows that satisfy every pair, unlike SUMIFS which needs a sum range up front.
- •All criteria ranges must be the same size. A2:A100 paired with B2:B101 returns #VALUE! in Excel, so avoid mixing a bounded range with a whole-column reference like B:B.
- •Criteria shortcuts worth knowing: "" counts blank cells (including formulas that return an empty string), "<>" counts cells that are not blank, and "*" counts cells containing text, which excludes numbers, dates and blanks.