COUNTIF Formula Generator

Pick a range and a condition in plain words, and get the formula to paste — with the quotes and the ampersand where they belong.

Your formula

Same formula with English function names

The mistakes this saves you

  • Comparing against a cell needs an ampersand. Counting values above 10 is ">10", but counting values above whatever sits in B1 is ">"&B1. Writing ">B1" searches for that literal text, matches nothing and returns 0 — with no error to tell you.
  • A zero is not an error message. Every quoting mistake in COUNTIF fails the same silent way: the formula works, and the answer is wrong. That is why getting it right matters more here than in functions that break loudly.
  • Wildcards are literal text. Counting cells that contain "Paris" is "*Paris*". With the word in a cell it becomes "*"&B1&"*" — the stars stay in quotes and the cell is joined in.
  • Empty is not the same as not-equal-to-empty. "" counts blanks and "<>" counts everything that is not blank. The two are easy to swap.
  • The separator depends on your Excel. An English Excel wants commas; a French, Spanish or German one wants semicolons. Pick yours and the formula comes out ready to paste.
  • So does the function name. The same formula is COUNTIF, NB.SI or CONTAR.SI depending on the interface language. Both versions are shown.

Counting on more than one condition

COUNTIF takes exactly one. For two or more — sales in Paris and above 1000 — you need COUNTIFS, which takes range and criterion in pairs.