SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF and AVERAGEIFS
These functions are called Conditional Functions because they perform calculations only when specified conditions are met.
SUMIF Function
The SUMIF function adds values that meet a single condition.
Syntax
SUMIF(range, criteria, [sum_range])
| Argument | Description |
|---|---|
| range | Cells to evaluate. |
| criteria | Condition to check. |
| sum_range | Cells to add if condition is met. |
Example Data
| Student | Grade | Total Marks |
|---|---|---|
| Rahul | A | 450 |
| Priya | B | 390 |
| Amit | A | 470 |
Formula
=SUMIF(B2:B4,"A",C2:C4)
Result: 920
Excel adds only the marks of students whose grade is A.
SUMIFS Function
The SUMIFS function adds values that meet multiple conditions.
Syntax
SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
Example
| Name | Grade | Result | Marks |
|---|---|---|---|
| Rahul | A | Pass | 450 |
| Priya | A | Fail | 390 |
| Amit | A | Pass | 470 |
Formula
=SUMIFS(D2:D4,B2:B4,"A",C2:C4,"Pass")
Result: 920
COUNTIF Function
The COUNTIF function counts cells that meet a single condition.
Syntax
COUNTIF(range, criteria)
Formula
=COUNTIF(B2:B10,"A")
Counts how many students received Grade A.
COUNTIFS Function
The COUNTIFS function counts records matching multiple conditions.
Syntax
COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2,...)
Formula
=COUNTIFS(B2:B20,"A",C2:C20,"Pass")
Counts students who have Grade A and Result Pass.
AVERAGEIF Function
The AVERAGEIF function calculates the average of values that meet one condition.
Syntax
AVERAGEIF(range, criteria, [average_range])
Formula
=AVERAGEIF(B2:B20,"A",C2:C20)
Calculates the average marks of students who have Grade A.
AVERAGEIFS Function
The AVERAGEIFS function calculates averages using multiple conditions.
Syntax
AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2,...)
Formula
=AVERAGEIFS(D2:D20,B2:B20,"A",C2:C20,"Pass")
Calculates average marks of students who are Grade A and Pass.
Comparison of Conditional Functions
| Function | Purpose | Single Condition | Multiple Conditions |
|---|---|---|---|
| SUMIF | Adds values | ✔ | ✘ |
| SUMIFS | Adds values | ✔ | ✔ |
| COUNTIF | Counts records | ✔ | ✘ |
| COUNTIFS | Counts records | ✔ | ✔ |
| AVERAGEIF | Calculates average | ✔ | ✘ |
| AVERAGEIFS | Calculates average | ✔ | ✔ |