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.

how to use sumif function in excel

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.

sumif example byref

(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.

sumif formula in excel

(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

how to add the number from sumif in excel

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

add the from sumif

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

how to use sumif function with two criteria in excel

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.

Download Workbook

How to Use Sumifs Function in MS Excel?

Leave a Reply

Your email address will not be published. Required fields are marked *