SUMIFS, AVERAGEIFS & COUNTIFS Function in Excel
Subscribe to SUMIFS, AVERAGEIFS, COUNTIFS on YouTube
Advance Excel Functions - we use functions like sum, average, count, if and so on in our daily work in excel. Though we are pretty comfortable with these function we are limited to get the results basis of criteria. With the help of combining IF, IFS to SUM, AVERAGE, COUNT we can acheive the results basis of certain criteria.
| 1. SUMIF | =SUMIF(range, criteria, [sum_range]) |
|---|---|
| 2. SUMIFS | =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2…… |
| 3. AVERAGEIF | =AVERAGEIF(range, criteria, [average_range]) |
| 4.AVERAGEIFS | =AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2…… |
| 5. COUNTIF | =COUNTIF(range, criteria) |
| 6. COUNTIFS | =COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2…… |
Let us see few examples on these functions.
| Region | Rep | Item | Units | Unit Cost | Amount |
|---|---|---|---|---|---|
| East | Jones | Pencil | 95 | $4.99 | $474.05 |
| Central | Kivell | Binder | 50 | $19.99 | $999.50 |
| Central | Jardine | Pencil | 36 | $4.99 | $179.64 |
| Central | Gill | Pen | 27 | $19.99 | $539.73 |
| West | Sorvino | Pencil | 56 | $2.99 | $167.44 |
| East | Jones | Binder | 60 | $19.99 | $1,199.40 |
| Central | Andrews | Pencil | 75 | $4.99 | $374.25 |
| Central | Jardine | Pencil | 90 | $4.99 | $449.10 |
| West | Thompson | Pencil | 32 | $2.99 | $95.68 |
| East | Jones | Binder | 60 | $19.99 | $1,199.40 |
| Central | Morgan | Pencil | 90 | $4.99 | $449.10 |
| East | Howard | Binder | 29 | $19.99 | $579.71 |
Let us now calculate the Total Sum and Average sum by Rep, Region, Items
=SUMIFS($G$3:$G$14,$C$3:$C$14,"Jones",$D$3:$D$14,"Pencil",$B$3:$B$14,"East")
With this example we are trying to look business in Amount for Rep "Jones" and criteria we applied in the formula are
- $G$3:$G$14 - Array for Sum Range - Amount
- $C$3:$C$14 - Array for Criteria Range1 - Rep "Jones"
- $D$3:$D$14 - Array for Criteria Range2 - Item "Pencil"
- $B$3:$B$14 - Array for Criteria Range3 - Region "East"
The result we get is $474.05
=IFERROR(AVERAGEIFS($G$3:$G$14,$D$3:$D$14,"Pencil",$B$3:$B$14,"East",$C$3:$C$14,"Jones"),0)
- $G$3:$G$14 - Array for Average Range - Amount
- $D$3:$D$14 - Array for Criteria Range1 - Item "Pencil"
- $C$3:$C$14 - Array for Criteria Range1 - Rep "Jones"
- $B$3:$B$14 - Array for Criteria Range3 - Region "East"
Now let us check for Countifs function
=COUNTIFS($B$3:$B$14,"EAST",$D$3:$D$14,"Pencil",$C$3:$C$14,"Jones")
In this example we are counting the number of line items in above table for Jones for Item Pencil sold in region East
- $B$3:$B$14 - Array for Criteria Range1 - Region "EAST"
- $D$3:$D$14 - Array for Criteria Range2 - Item "Pencil"
- $C$3:$C$14 - Array for Criteria Range3 - Rep "Jones"
No comments:
Post a Comment