Conditional Functions: SUMIF, COUNTIF and Others
These add, count or average only the cells that meet a condition. They are among the most tested Excel functions.
The five core functions
| Function | Syntax | Example | Result |
|---|---|---|---|
| COUNTIF | =COUNTIF(range, criteria) | =COUNTIF(B2:B9,"Jammu") | 3 |
=COUNTIF(C2:C9,">=60") | 6 | ||
| COUNTIFS | =COUNTIFS(range1, crit1, range2, crit2, ...) | =COUNTIFS(B2:B9,"Jammu",C2:C9,">=70") | 2 |
| SUMIF | =SUMIF(range, criteria, [sum_range]) | =SUMIF(B2:B9,"Jammu",C2:C9) | 228 |
=SUMIF(C2:C9,">=60",D2:D9) | 18500 | ||
| SUMIFS | =SUMIFS(sum_range, range1, crit1, range2, crit2, ...) | =SUMIFS(D2:D9,B2:B9,"Anantnag") | 6000 |
| AVERAGEIF | =AVERAGEIF(range, criteria, [average_range]) | =AVERAGEIF(B2:B9,"Srinagar",C2:C9) | 83 |
| AVERAGEIFS | =AVERAGEIFS(avg_range, range1, crit1, ...) | ||
| MAXIFS / MINIFS (2019+) | =MAXIFS(max_range, range1, crit1, ...) | =MAXIFS(C2:C9,B2:B9,"Jammu") | 92 |
Writing criteria
| Criteria | Meaning |
|---|---|
"Jammu" | Equal to Jammu (not case-sensitive) |
">=60" | Comparison goes inside quotes |
"<>Jammu" | Not equal |
"J*" | Starts with J (wildcard) |
"?????" | Exactly five characters |
">"&F1 | Greater than the value in cell F1 (join with &) |
"<>" | Not blank |
"" | Blank |
">"&AVERAGE(C2:C9) | Greater than the average |
OR conditions
SUMIFS and COUNTIFS combine conditions with AND. For OR, add separate functions:
=COUNTIF(B2:B9,"Jammu")+COUNTIF(B2:B9,"Srinagar") → 5
or count several values at once with =SUM(COUNTIF(B2:B9,{"Jammu","Srinagar"})) → 5.
Between two values
=COUNTIFS(C2:C9,">=60",C2:C9,"<=80") counts marks from 60 to 80: 3 (78, 65, 71).
Remember
- Text criteria are not case-sensitive. For case-sensitive counting use SUMPRODUCT with EXACT.
- Ranges in the IFS family of functions must all be the same size.
- A criteria range and the sum range must line up row by row.
- A criterion like
"1"matches both the number 1 and the text 1, so COUNTIF does not tell them apart.
Keep practising
Notes are only half the work. Test yourself with questions and discuss doubts with other aspirants.
Prepared for exam revision from standard computer textbooks, Microsoft documentation and official sources. Features differ between Excel and Word versions (2016, 2019, 2021, 365) so check which version your exam follows. Found a mistake? Tell us.