How to Use Sumifs Function in MS Excel?

SUMIFS Function

SUMIFS is Microsoft Excel Function, it is used for add all numbers in the range of cells in excel with multiple criteria.

Inbuilt Function

The SUMIFS function is an inbuilt function in Microsoft Excel which is categorized as Math & Trig Function.

Operators & Wildcards

We can use operator (<, >, =, <>,>=, <=) and Wildcards (*, ?) for SUMIFS function. Operators are used for logical operations and wildcards are used for the partial match.

Availability

SUMIFS function available in Excel 2007 and its later version.

How to use Sumifs Function in MS Excel

The syntax of SUMIFS Function

There are 255 arguments in SUMIFS Function. Except for first three syntaxes, all syntax is optional.

=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,……..,criteria_range127,criteria127)

(A). Sum_Range – The Sum_Range which is used for the sum.

(B). Criteria_Range1 – The first range which is used to evaluate.

(C). Criteria1 – The criteria1 used based on Criteria_Range1

(D). Criteria_Range2 – This is optional. The Second range which is used to evaluate.

(E). Criteria2 – This is optional. The criteria2 used based on Criteria_Range2

 

Criteria Apply

We sum based on criteria value in cells. We use criteria from two methods. We use multiple criteria for SUMIFS.

(I). ByVal – =SUMIFS($A$2:$A$10,$B$2:$B$10,”A”), this is fixed criteria value will not be changed while we use this for other criteria. We have to always change criteria manually.

(II). ByRef – =SUMIFS($A$2:$A$10,$B$2:$B$10,D2), this is flexible criteria value will change while using this for other criteria.

 

Rules for using SUMIFS

(A). Each Range must equal as Sum_Range.

(B). Non-Numeric criteria must be in double quotes i.e “Criteria”, but numeric criteria doesn’t require double quotes except with operator i.e “>180”.

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

NOTE

(A). We cannot use Vertical Range and Horizontal Range as Criteria Range for SUMIFS Function. If we use it then the SUMIFS Function gives an error.

how to use sumifs function in excel with multiple criteria

(B) When we criteria as text its length, should not be more than 255 characters.

Download Workbook

How to Use Sumif Function in MS Excel?

Leave a Reply

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