What is the Excel SUMIF Function
SUMIF is a function to add numbers within a range that meet single criteria. The criteria could be a number, text, or date, support for the logical operator (<, >, =, <>) and wildcard (?, *) for partial matching.
SUMIF Syntax
SUMIF(range, criteria, [sum_range])
range, cell/range to be evaluated by criteria.
criteria, determine which cells to add, could be numbers, text or functions. If a number no need for double quotation marks. Have to be enclosed in double quotation marks if a text or a number, but using “>” or “<” sign.
If the data being evaluated is text, criteria could use a wildcard (?, *). The question mark “?”represents a single character and the asterisk “*” represents more than one character.
sum_range, optional, the cells to add together, if omitted, the cells in range argument are added together instead.
How to Use SUMIF Function in Excel
The SUMIF function and range argument is a number
For example, there are data such as the image below. Area information in column A, iPhone name variant in column B, capacity in column C and sales in columns D.
What are the results of the following SUMIF functions?
SUMIF Formula #1
=SUMIF(C2:C14,">=128",D2:D14)
All argument used. Criteria “>=128” is a number, using greater than or equal to sign “>=” then the criteria are enclosed in double quotation marks, although it is a number.
There are five cells in the range C2:C14 which has a number greater than or equal to 128, because the sum_range argument exists, then data in range D2:D14 in the same line added
The formula above answers the question “What is the total sales of the iPhone in all areas about RAM size is greater than or equal to 128 GB?”.
The result is 33,481
SUMIF Formula #2
=SUMIF(C2:C14,">=128")
The formula is like the first formula. The only difference is no sum_range argument, then the data in the range C2:C14 that match the criteria added.
The result is 1,024
SUMIF Formula #3
=SUMIF(C2:C14,128,D2:D14)
Criteria argument is a number, no greater than or less than sign; the numbers can be written with no double quotation marks that enclose it. There are two data sales for 128 GB iPhone
The formula above answers the question “What is the total sales of 128GB iPhone in all areas “?
The result is 12,012
SUMIF Formula #4
=SUMIF(C2:C14,">="&C9,D2:D14)
The formula above is the same as the SUMIF #1. Criteria can be written in various ways, the number in the criteria argument of SUMIF #1 written directly in the formula. While the number in the criteria argument of SUMIF #4 written by combining data in a cell. The result is the same.
The SUMIF function and range argument is a text
With the same data as the previous example. What are the results of each of the following SUMIF functions?
SUMIF Formula #A
=SUMIF(B2:B14,"IPHONE X",D2:D14)
The formula above answers the question “What is the total sales of the iPhone X in all areas?”
There are 2 IPHONE X data, cell B2, and B7; the added data is cell D2 and D7. Notice the writing of criteria argument; Excel
considers the uppercase or the lowercase just the same (case insensitive).
The result is 12,440
SUMIF Formula #B
=SUMIF(B2:B14,"iphone 7*",D2:D14)
There is a wildcard character “*” after the word “iphone 7”. Excel will look for data beginning with the word “iphone 7”, no matter the word after it, there could be nothing, there could be another word.
Sales data show there are 2 data with the beginning word “iPhone”, whatever word after it, “iPhone 7” and “iPhone 7 Plus”.
The formula above answers the question “What is the total sales of all variants of iPhone 7, regular size or plus size?”
The result is 29,237
SUMIF Formula #C
=SUMIF(B2:B14,"*plus",D2:D14)
Wildcard character again as the previous formula, the difference is wildcard is in front with the word “plus”, this means excel looking for data ending with the word “plus”, no matter what’s in front of the word.
The formula above answers the question “What is the total sales of all the plus size iPhone?”
The result is 23,210
SUMIF Formula #D
=SUMIF(B2:B14,"iphone x")
Look like the SUMIF formula #A but no sum_range argument, so Excel adds data in the range argument, and all is text then the sum results is 0.
SUMIF Limitations
The limitation of the SUMIF function only handles one criterion. For more than one, please use SUMIFS function or using SUMIF function and a trick.
Additional helper columns and logical function able to make SUMIF function handle two criteria or more. Please look at SUMIF example below for an example of using the SUMIF function in various circumstances.
SUMIF Example
Another article using SUMIF Function
