In this lesson we will learn how the SUMIF function works in Excel. The SUMIF function returns the sum of the cells that meet a single criteria.
The criteria used in SUMIF function can be based on dates, numbers, text, and other conditions. Logical operators (>,<,<>,=) and wildcards (*,?) for partial matching are supported by SUMIF 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.
=SUMIF(range, criteria, [sum_range])
- range (required argument) – This is the range to evaluate with the associated criteria.
- criteria (required argument) – The condition to be used on range.
- sum_range (optional argument) – a range of cells containing numeric values which are to be added together if the criteria are satisfied. If sum_range is omitted, all cells in the range are added together instead.
In the below example we will see how the SUMIF 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 range = 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.
criteria = “*”&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.
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.
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 range, it will sum the price. As SUMIF function is not case-sensitive, it will consider both “coat” and “COAT”.
Now on the same example table we just need the sum of the dresses which are not is the specific category mentioned in each cell of column D. Below is the formula we have used here with wildcard Character(*) and logical operator(<>) both enclosed within double quotes.
Here for example cell D2 contains the category “coat”. Now the sum of price will be calculated for all dresses in the range(column A) which doesn’t end with a “coat”. That is, the dresses ending with a “coat” (A2 = Black coat, A5= Yellow coat & A8=Orange COAT), will be excluded. As SUMIF 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.
- 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 SUMIF function is case-insensitive.