SUMIFS

Rational

Sum range of cells based on more than one 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 specifications (Grade, Item Type, Inventory) is in three different columns 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., ~?, ~*). Criteria a*e will match with all cells containing a string beginning with “a” and ending in “e” like a text “abide” or “abate”.