Monday, November 14, 2011

Excel Function : SUM IF

Item Date Cost
Apple 01-Jan-11 300
Banana 10-May-11 50
Grape 01-Feb-98 200
Pumpking 01-Mar-98 150
Apple 05-Jan-98 40
Apple 01-Jun-98 100
Banana 01-Apr-98 350
Grape 01-Mar-98 100
Pumpking 01-May-98 250
Total of all Apples 440 = SUMIF(A2:A10,"Apple",C2:C10)
Total of all Banana 400 = SUMIF(A2:A10,"Banana",C2:C10)
Total of all Grape 300 = SUMIF(A2:A10,"Grape",C2:C10)
Total of all Pumpking 400 = SUMIF(A2:A10,"Pumpking",C2:C10)
SUM IF
This function adds the value of items which match criteria set by the user.
Syntax              
=SUMIF(RangeOfThingsToBeExamined,CriteriaToBeMatched,RangeOfValuesToTotal)
=SUMIF(C4:C12,"Brakes",E4:E12) This examines the names of products in C4:C12.
It then identifies the entries for Brakes.
It then totals the respective figures in E4:E12
 =SUMIF(E4:E12,">=100") This examines the values in E4:E12.
If the value is >=100 the value is added to the total.
Formatting              
No special formatting is needed.