COUNTIF

Criteria from a cell

Spreadsheet in your browser
Lesson concept What you'll do
1 / 6

Count the sales that reach a target typed in a cell.

Join the sign to the cell: ">="&F1, so changing F1 changes the count.

Typing formulas is easiest on a computer with a keyboard.

A1
ABCDEFG
1NameSalesTarget610
2Tariq720Reached it
3Nadia230
4Daniel310
5Quinn450
6Jamal230
7Isla750
8Kofi610
9Yusuf300
10Sam950
11Rosa370
12
13
14
15
Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.
Study notes

How COUNTIF and SUMIFS works

  • Why take the condition from a cell?

    =COUNTIF(B2:B11,">=500") works, but 500 is typed into the formula. Put the target in a cell instead, and the count updates whenever the target changes.

  • How do you join the operator and the cell?

    Keep the operator in quotes and join the cell with &:

    =COUNTIF(B2:B11,">="&F1)

  • Why not ">=F1"?

    Inside quotes, F1 is just the letters F and 1, not a cell. Excel compares the sales with the text "F1" and counts nothing. The same goes for SUMIF, AVERAGEIF and the IFS versions.

Know someone fighting with a spreadsheet? Send them this page.