In this lesson we will learn how the SUMIFS function works in Excel. The SUMIFS function returns the sum of the cells that meet a single or multiple criteria in the same or different ranges.
The criteria used in SUMIFS function can be based on dates, numbers, text, and other conditions. Logical operators (>,<,<>,=) and wildcards (*,?) for partial matching are supported by SUMIFS function.
Here is the difference between SUMIF and SUMIFS –
SUMIF is designed to calculate the sum of cells with a single condition in one range, whereas SUMIFS can evaluate different conditions in the same or different ranges.
=SUMIFS(sum_range, range1, criteria1, [range2], [criteria2], ...)
- sum_range (required argument) – a range of cells containing numeric values which are to be added together if the criteria are satisfied.
- range1 (required argument) – This is the first range to evaluate with the associated criteria.
- criteria1 (required argument) – The condition to be used on range1.
- range2, criteria2, … (optional argument) – Additional ranges and their associated conditions. SUMIFS function allows up to 127 range/criteria pairs.
In the below example we will see how the SUMIFS function is used to get the sum of price for each mentioned category.
We have a table containing the list of different type of dresses along with their price.
Now we need the sum of price for each different category of dress using the below formula.
Here sum_range = B$2:B$10. This is the list of all Prices. We used $ symbol to fix the column range in the formula when dragged for the following cells in column E.
range1 = A$2:A$10. This is the list of all Dresses. We used $ symbol to fix the column range in the formula when dragged for the following cells in column E.
criteria1 = “*”&D2(this will be D3, D4 and so on for the following cells). It denotes any number of characters ending with the value(category) mentioned in cell D2.
For example cell D2 contains the category “coat”. Now for each dress ending with a “coat” (A2 = Black coat, A5= Yellow coat & A8=Orange COAT) in the list of dresses mentioned in range1, it will sum the price. As SUMIFS function is not case-sensitive, it will consider both “coat” and “COAT”.
- Non-numeric criteria needs to be enclosed in double quotes but it’s not needed for numeric criteria.
- The wildcard characters ? and * can be used in criteria for partial match. A question mark (?) matches any one character and an asterisk(*) matches any sequence of characters.
- To find a literal question mark or asterisk, use a tilde (~) in front of the question mark or asterisk (i.e. ~?, ~*).
- #VALUE! error occurs if the supplied criteria arguments are text strings more than 255 characters long.
- Each additional range should include the same number of rows and columns as the sum_range argument.
- SUMIF and SUMIFS can handle ranges, but can not handle arrays. This means you can’t use other functions like MONTH on the criteria range, since the result is an array. Use the SUMPRODUCT function, if you need this functionality.
- The Excel SUMIFS function is case-insensitive.