SUMIFS

Sum range of cells based on multiple criteria in Excel
Adds the cells specified by a multiple criteria

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:

To add multiple numbers in Excel

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.

Read More »
Sum range of cells based on the rule in Excel

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.

Read More »
Multiply the corresponding value and sum in Excel

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.

Read More »