How to Use Sumifs Function in MS Excel?
SUMIFS is Microsoft Excel Function, it is used for add all numbers in the range of cells in excel with multiple criteria.
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.
SUMIFS function available in Excel 2007 and its later version.
The syntax of SUMIFS Function
There are 255 arguments in SUMIFS Function. Except for first three syntaxes, all syntax is optional.
(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
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.
(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.
(B) When we criteria as text its length, should not be more than 255 characters.