How to Use Sumif Function in MS Excel?
SUMIF Function
SUMIF is Microsoft Excel Function, it is used for add all numbers in range of cells in excel with criteria.
Inbuilt Function
SUMIF function is an inbuilt function in Microsoft Excel which is categorized as Math & Trig Function.
Operators & Wildcards
We can use operator (<, >, =, <>,>=, <=) and Wildcards (*, ?) for SUMIF function. Operators are used for logical operations and wildcards are used for partial match.
Syntax for SUMIF
There are three arguments in Sumif Function.
=SUMIF(range,criteria,[sum_range])
(A). Range – The Range of cells that we apply criteria against.
(B). Criteria – We sum based on criteria value in cells. We use criteria from two method.
(I). ByVal – =SUMIF($A$2:$A$10,”A”,$B$2:$B$10), this is fixed criteria value will not be change while we use this for other criteria. We have to always change criteria manually.
(II). ByRef – =SUMIF($A$2:$A$10,D2,$B$2:$B$10), this is flexible criteria value will change while use this for other criteria.
(C). Sum_Range – The Sum_Range which is used for sum and this is optional arguments when Range to be used for sum against criteria then we don’t need to give this arguments otherwise we have to give Sum_Range for sum in formula.
How to Use Sumif Function in MS Excel?
Rules for SUMIF
(A). Non-Numeric criteria must be in double quotes i.e “Criteria”, but numeric criteria doesn’t required double quotes except with operator i.e “>180”.
(B). The wildcards (*, ?) can be used as criteria in double quotes. Star (*) represent any series of characters and question mark (?) to represent any single of characters.
(C). Criteria length should not be more than 255 characters.
SUMIF Example
(A). Example 1
In this example we have to add the total Score of each Name so we use SUMIF function. We use this formula =SUMIF($A$2:$A$21,D2,$B$2:$B$21).
(B). Example 2
In this example, we have to add the Total Score of above 50 so we use SUMIF function. We use this formula =SUMIF($A$2:$A$21,”>50″). In this formula, we have not given third syntax because we added the number of Range.
(C). Example 3
In this example, we have to add based on two criteria so we use SUMIF and SUM function. =SUM(SUMIF($A$2:$A$21,{“Akash”,”Sonu”},$B$2:$B$21)) in this syntax SUMIF add the number of each criteria after that SUM add the number.