Math
Counting numbers above a cutoff
Definition
Counting numbers above a cutoff means giving COUNTIF a number column and a greater-than test.
Syntax
=COUNTIF(range, criteria)
Arguments
| Argument | Meaning |
|---|---|
range |
The block of numbers to scan. |
criteria |
The test each cell must meet, such as ">6", including the quotes. |
Worked example
A bin list stores counts in B2:B5. Values above 6 should be counted.
| A: Bin | B: Count |
|---|---|
| A | 10 |
| B | 4 |
| C | 12 |
| D | 8 |
Formula: =COUNTIF(B2:B5,">6")
Result: 3. 10, 12, and 8 are above 6; 4 is not.
Practice
Cells A2:A5 hold 3, 9, 7, and 2. In B2, write a formula that counts how many cells are greater than 5.
- In B2, write a COUNTIF formula that counts how many cells in A2:A5 are greater than 5.
- B2 should show how many cells in A2:A5 are greater than 5.
Step 1 of 2
Enter =COUNTIF(A2:A5,">5") in B2.
9 and 7 are greater than 5, so the count is 2.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
B2 uses COUNTIF.
B2 now shows 2.
The next lesson is Testing a number against a cutoff.
Related functions
COUNTIF is the function that counts the numbers that pass that greater-than test.
Common mistake
Leaving the quotes off the test, as in =COUNTIF(B2:B5,>6): the greater-than sign is then read as math, not as criteria, and the count is wrong.