Sponsored Link

Sponsored Link

Tuesday, March 17, 2009

SUMIF function to Summarize data

This function works like a simple Sum function. But you can add a criteria to it. It is very useful when you want to find out sum of values for certain items from the huge list of items. As we go ahead I will take you through the formula syntax and example. Also, if we use SUMIF and COUNTIF(which is another  similar function in MS Excel) together than we can calculate average based on criteria. In my next post I will describe COUNTIF.

Formula: =SUMIF(RangeOfThingsToBeExamined ,CriteriaToBeMatched ,RangeOfValuesToTotal)

Here is example below which show baby day-wise baby product sales.

Date Item Quantity Cost Amount
17-Mar-09 Dolls 44 148 6512
12-Jan-09 Hairpin 8 57 456
15-Apr-08 Diapers 8 176 1408
28-Jun-08 Baby Oil 29 124 3596
24-Nov-09 Dolls 43 120 5160
16-Oct-09 Diapers 34 159 5406
24-Mar-09 Baby Oil 39 196 7644
16-Jan-09 Diapers 26 154 4004
17-Apr-08 Dolls 28 84 2352
4-Jul-08 Baby Oil 39 82 3198
28-Nov-09 Diapers 36 101 3636
20-Oct-09 Cap 30 117 3510
3-Apr-09 Dolls 17 122 2074
23-Jan-09 Cap 45 55 2475
25-Apr-08 Dolls 6 93 558

The task here is to summarize report item-wise and bifurcate total in two part, part one where quantity is sold more than 25 and other part is where quantity is less or equal to 25. There are many ways to achieve this task. But here we will try SUMIF function to achieve it. Another way is PIVOT which I will soon post as another post.

Table below shows part one where we want to summarize the sales table item-wise.


Amount total Formula
Dolls 16656 =SUMIF(B2:B16,C19,E2:E16)
Hairpin 456 =SUMIF(B3:B17,C20,E3:E17)
Diapers 14454 =SUMIF(B4:B18,C21,E4:E18)
Baby Oil 14438 =SUMIF(B5:B19,C22,E5:E19)
Cap 5985 =SUMIF(B6:B20,C23,E6:E20)

Range part of SUMIF covers B2:B16 examines which cells has Item Dolls, B22 has Doll which it takes as Criteria, SumRange which is from E2:E16 add up all the respective cell in amount column which has "Dolls" in B column

Table below shows part two our task where we are summarize amount total where quantity greater than 25 or less than equal to 25

Criteria Total Formula
Total of Amount for Quatity >25 47493 =SUMIF(C2:C16,">25",E2:E16)
Total of Amount for Quatity <=25 4496 =SUMIF(C3:C17,"<=25",E3:E17)

Range part of SUMIF covers C2:C16 examines quantity column which cells has number greater than 25. Here Criteria is a string ">25", SumRange which is from E2:E16 add up all the respective cell where quantity is greater than 25.

I am sure this post is helpful in understanding SUMIF function. Post your comments

If you have any further doubt with SUMIF, please download example of SUMIF function or you can write me. I assure you prompt response.

SUMIF Example download

If you are first time visitor, please subscribe via email to receive updates, add-ins, e-books and lot more.

We assure you knowledge, not SPAM!


Post a Comment