COUNTIFS Formula Generator

Set up to three conditions in plain words and get the formula, with every range and criterion paired up in the right order.

Your formula

Same formula with English function names

What COUNTIFS asks of you

  • Ranges and criteria come in pairs. Range, criterion, range, criterion. Miscount the arguments and Excel either complains or, worse, pairs them up differently from what you meant.
  • Every range must be the same shape. A2:A100 and B2:B50 cannot be combined; COUNTIFS needs ranges of identical height. Mismatched ranges give a #VALUE! error rather than a wrong number, which at least is visible.
  • The conditions are joined by AND, never OR. Rows are counted only when every condition holds. Counting rows matching one condition or another needs two COUNTIFS added together, minus the overlap.
  • Comparing against a cell still needs the ampersand. ">"&B1, exactly as with COUNTIF, in every one of the pairs.
  • Leave a condition blank to drop it. Two conditions filled and one empty builds a two-condition formula, rather than an argument with nothing in it.

When one condition is enough

COUNTIFS works perfectly well with a single pair, but COUNTIF is shorter and reads better. Use COUNTIFS from the second condition onwards.