SUMIF Function in Excel

SUMIF is the function used to sum the values according to a single criterion. The SUMIF function is categorized under excel math & trigonometry function. Using this function, you can find the sum numbers applying a condition within a range. It will sum up cells that meet given criteria. The criteria are based on dates, numbers, and text. This function is used to find the sum of particular numbers within a large data set.

 

Syntax:

=SUMIF(range,criteria,[sum_range])

The formula uses the following arguments:

Range:
The range of cells that you want to apply the criteria against.

Criteria:
The criteria used to determine which cell to add.

Sum_range (Optional argument):
It indicates the range of cells to be added together.

How to use the SUMIF function in Excel?

SUMIF supports logical operators (>, <, <>, =) and wildcards (*,?) for particular matching. Let us understand the SUMIF excel function with the help of the following examples.

 

# Numeric number:
Let’s explore how to use SUMIF function based on numbers that meet specific criteria.

Example #1
In the following worksheet shown there are some numeric number. SUMIF is configured to sum values which is greater than 50. So, the formula will be =SUMIF(B2:B7,”>”&50). And the returns value will be 205.

Example #2
In the below example SUMIF is configured to sum values which is less than or equal to 20. So, the formula will be =SUMIF(B2:B7,”<=20"). And the returns value will be 46.

           

Example #3
The following worksheet shown that there are three arguments, last argument is the range of sum (B2:B7). SUMIF is configured to sum value which is contain 2020 in the range A2:A7. So formula will be =SUMIF(A2:A7,2020,B2:B7). And the returns value will be 23.4.

Example #4
A value from another cell can be included in criteria using concatenation. The SUMIF will return the sum of all sales over the value in E3. The formula in E4 is =SUMIF(B2:B7,”>”&E3) and the returns will be 395.

 

# Text strings:
Let’s explore how to use SUMIF function based on text strings that meet specific criteria. Notice that which is text, must be always enclosed in double quotation marks.

Example #1
In the following worksheet, SUMIF function is configured to sum value (range B2:B8) which is contained exactly East in the range A2:A8. The formula will be =SUMIF(A1:A8,”East”,B1:B8) and the returns will be 70.

 

Example #2
To express “not equal to criteria use “<>” operator by double quotes. In the below worksheet shown that if we need to sum which is not Apple from range of A1:A8 and value range is B1:B8 then the formula will be =SUM(A1:A8,”<>Apple”,B1:B8) and the returns will be 78.

 

Example #3
In the below format SUMIF function is configured to sum value in the range B1:B8 if the corresponding cells in the range A1:A8 contain exactly Banana or Cherry. Then the formula will be =SUMIF(A1:A8,”Banana”,B1:B8)+SUMIF(A1:A8,”Cherry”,B1:B8) and the returns will be 64.

 

# Wildcards:
The function supports wildcards, as seen in the below example

Example #1
In the below example the sum function is configured to sum the character which is beginning with “Ora” in the range of A1:A7. An asterisk (*) matches the rest. So the formula will be =SUMIF(A1:A7,”Ora*”,B1:B7). The returns will be 140.

 

Example #2
SUMIF function is configured to sum values in the range B1:B7 if the corresponding cells in the range A1:A7 contain a character “ge” at the end. Here also an asterisk (*) matches the series of characters. The formula will be =SUM(A1:A7,”*ge”,B1:B7) and returns will be 110.

 

Example #3
In the below format shown that SUMIF function is configured to sum values in the range B1:B7 if the corresponding cells in the range A1:A7 contain character (Coconut) with a number where a question mark (?) matches one character. So the formula will be =SUMIF(A1:A7,”Coconut?”,B1:B7) and the returns will be 90.

 

# Dates:
Use the SUMIF function in excel to sum cells on based on a valid date in another cell or use the date function. Please note that DATE function in the below examples accepts three arguments. i.e. year, month and day.

Example #1
In the below format SUMIF function is configured to sum the cell which is before June 12^th, 2020. The formula will be =SUM(A1:A7,”<”&DATE(2020,6,12),B1:B7). The returns value will be 180.

 

Example #2
The best way to use SUMIF with dates is to refer to a valid date in another cell (D6) which is greater than or equal to March 18^th, 2020. The formula will be = SUMIF (A1:A7 ,” >=” &D6, B1:B7) . The returns will be 85.