These functions help you sum, count, or average values based on multiple conditions.
The SUMIFS function adds up values that meet multiple criteria.
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
sum_range → The range of values to sum.criteria_range1, criteria1 → The first condition.criteria_range2, criteria2 → (Optional) Additional conditions.Scenario : Sum sales where the region is "North" and product is "Laptop".
| Region | Product | Sales |
|---|---|---|
| North | Laptop | 500 |
| South | Laptop | 300 |
| North | Phone | 400 |
| North | Laptop | 600 |
=SUMIFS(C2:C5, A2:A5, "North", B2:B5, "Laptop")
* Result : 500 + 600 = 1100
The COUNTIFS function counts the number of values that meet multiple criteria.
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
criteria_range1, criteria1 → The first condition.criteria_range2, criteria2 → (Optional) Additional conditions.Scenario : Count how many times "Laptop" was sold in "North" region.
=COUNTIFS(A2:A5, "North", B2:B5, "Laptop")
* Result: 2
The AVERAGEIFS function calculates the average of values that meet multiple conditions.
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
average_range → The range of values to average.criteria_range1, criteria1 → The first condition.criteria_range2, criteria2 → (Optional) Additional conditions.Scenario: Find the average sales where the region is "North" and product is "Laptop".
=AVERAGEIFS(C2:C5, A2:A5, "North", B2:B5, "Laptop")
* Result : (500 + 600) / 2 = 550
| Function | Purpose | Example | Result |
|---|---|---|---|
SUMIFS |
Sum values with multiple conditions | =SUMIFS(C2:C5, A2:A5, "North", B2:B5, "Laptop") |
1100 |
COUNTIFS |
Count occurrences with multiple conditions | =COUNTIFS(A2:A5, "North", B2:B5, "Laptop") |
2 |
AVERAGEIFS |
Find the average with multiple conditions | =AVERAGEIFS(C2:C5, A2:A5, "North", B2:B5, "Laptop") |
550 |
* Efficient for large datasets
* Handles multiple conditions
* Reduces the need for manual filtering