Rational
Sum range of cells based on multiple criteria.
Description
The Excel SUMIFS function return an addition of numerical value based on one or multiple given criteria. SUMIFS cells meet conditions based on the match of dates, numbers, and text format against the reference.
Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Parameters
sum_range – The range of cells to evaluate for addition against criteria
range1 – The first range to evaluate containing criteria1
criteria1 – The criteria to apply for addition in range1
range2 [optional] – The second range to evaluate containing criteria2
criteria2 [optional] – The second criteria to apply for addition in range2
Return Value
Sum of values based on one or more criteria
Key Notes
The SUMIFS function is a plural variation of SUMIF as it accepts multiple arguments to sum one or multiple ranges or array of numbers.
For example, suppose that three specification (Grade, Item Type, Inventory) is in three different column and user want to add an inventory of each Item Type based on a specific grade, the formula can be following:
=SUMIFS($D$4:$D$22,$C$4:$C$22,G6,$B$4:$B$22,F6)
sum_range – Inventory figure for each item type ($D$4:$D$22)
range1 – List of Item Type for each product type ($C$4:$C$22)
criteria1 – Item Type to select for addition (G6)
range1 – List of the grade for each product type ($B$4:$B$22)
criteria1 – Grade to select for addition (F6)
- Add products in inventory that is of grade 1 & 2 separately, the addition only takes place when both criteria are fulfilled (Inventory type and Grade type).
- SUMIFS can handle between 1 to 127 range or criterion pairs.
- For text criteria that include logical operators (>,<,<>,=) or wildcards symbols (*,?) for partial matches must be enclosed in double quotation marks (“) like “<30”.
- The user can find a literal question mark or asterisk by using a tilde (~) symbol before a question mark or asterisk (i.e., ~?, ~*). A criteria a*a will match with all cells containing a string beginning with “a” and ending in “a” like a text “abide” or “abate”.
Keep Reading Similar Functions:
SUM
The SUM function returns a number after adding multiple numeric arguments together =SUM(5,10,15) the Excel add numbers together and returns 30.
SUMPRODUCT
The SUMPRODUCT function returns a sum of the product of numbers together distributed in multiplies ranges or arrays, an easier way to add multiple integers.
SUMIF
The SUMIF function returns a sum of number based on given criteria, like =SUMIF({2;8;10;3;5},”>5″) add numbers that are only more than 5 and returns 18.