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
				51989

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.

Items	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)
	51989

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!