BeginnerStatistical Functions
Count orders above a threshold with COUNTIF
Count how many rows meet a numeric condition.
COUNTIF
5 min15 points
COUNTIF
Counts cells that meet one condition.
Syntax
COUNTIF(range, criteria)
Statistical Functions.
Practise COUNTIF7 interactive exercises, free, no account needed
A worked example
=COUNTIF(C2:C9,">2000")
Returns 4
Counts how many of the eight orders cleared the $2,000 free-freight threshold.
Where it goes wrong
The condition must be a text string when it contains an operator. Writing >2000 without quotes is a syntax error.
What the exercises catch
Each of these is a wrong turn the practice exercises recognise and name. They are the misconceptions, not the answers.
- COUNT counts every number regardless of value. You need the ones above 2000, which means a condition — that is what COUNTIF adds.Count orders above a threshold with COUNTIF
- The range shifts as you fill down, so by row 5 it is only searching the rows below and the earlier copies are invisible. The range being searched must stay still while the value being looked up moves.Spot duplicate order numbers with COUNTIF
- The average is right, but it hard-codes both figures you just calculated. Reference G3 and G2 so the whole summary updates together when an order is added.Build a regional summary from raw orders
- A lookup returns #N/A when nothing matches, which technically answers the question but leaves errors in your report. Counting occurrences gives you a clean 0 or 1 to test against.Find which customers are missing from a list
Practise COUNTIF
Knowing what a function does and being able to write it from memory are different skills. These close the gap.
All COUNTIF practice exercises →
- IntermediateData Cleaning
Spot duplicate order numbers with COUNTIF
Find rows whose order number appears more than once in the column.
COUNTIF · IF
9 min30 points - IntermediateData Analysis
Average one region without AVERAGEIF
Build a conditional average out of a conditional sum and a conditional count.
SUMIF · COUNTIF
9 min30 points - AdvancedData Analysis
Build a regional summary from raw orders
Produce a summary line combining count, total and average for one region.
COUNTIF · SUMIF
16 min40 points