What is SUMIF in Excel?
SUMIF in Excel function provides the sum of values of a certain cell range based on the criteria applied. For example, the formula “=SUMIF(B1:B5,“Pass”,F1:F5)” adds the values (marks) in the cell range F1:F5, which correspond to Pass.
In SUMIF, we can SUM-specific cells or groups based on one criterion only. SUMIF is available under the Formula bar and the Math & Trigonometry bar.
Key Highlights
- We can use SUMIF in Excel to add values with multiple criteria by combining them with the SUMIFS function.
- We can refer to the SUMIFS function as Nested SUMIF, also.
- When we omit sum_range, the SUMIF function in Excel sums up the cells in the particular range.
- We can use wildcard characters such as an asterisk (*) and question (?) marks in criteria related to the text.
- To find a literal question mark or asterisk, we must use a tilde (~) mark in front of the asterisk or question mark in this way— ~* or ~?
- We can use it while doing calculations of a large data table, thus saving time on manual calculations.
- Manual additions can lead to human errors, and SUMIF gives accurate results.
SUMIF in Excel Syntax
=SUMIF(range,criteria,[sum_range])
range: Cell range(column) in which Excel finds the data that meets the given criteria
Excel functions, formula, charts, formatting creating excel dashboard & others
criteria: Condition that the selected cell range must follow to perform the calculation
sum_range: Cell range from which values are added based on given criteria.
How to Use the SUMIF in Excel?
To understand the usage of the SUMIF function, let us consider a few examples.
You can download this SUMIF in Excel here –SUMIF in Excel
Example #1
The following table shows a list of branded equipment and their prices. Using the SUMIF function, we need to calculate the total amount spent by the country of Mexico on purchasing televisions.
We will apply the SUMIF formula in cell I7 to get Mexico’s total or gross sales.
Step 1: Write =SUMIF and double-click to select SUMIF.
Step 2: Now, select the range B7:B24 and put a comma to separate it from the criteria.
Step 3: Add Mexico in double quotations as the criteria and then put another comma to separate it from the sum column range.
Step 4: Select the range F7:F24 as it contains the Gross sales amount.
Step 5: Press the Enter key to get the below result.
The SUMIF function returns the Gross Sales of 376110 for Mexico.
Example #2
Consider the same data to find the Gross sales of Television for each country.
Step 1: Write the names of all the countries in column H
Step 2: Now, enter the SUMIF formula for the country of Mexico in cell I7 but with a $ symbol, as shown below
=SUMIF($B$7:$B$24,”Mexico”,$F$7:$F$24)
Note: We have added the $ symbol to lock the data range. It is because we must use the same cell range to calculate Gross sales for each country.
Step 3: Press the Enter key to obtain the Gross Sales for cell I7 (Mexico)
Step 4: Now, copy the formula of cell I7 to cells- I8, I9, and I10 to get the Gross sales for Canada, Germany, and France, as shown in the image below:
The image above shows that when we put the $ symbol, the range and sum_range remain the same for other cells.
The SUMIF function returns the Gross sales for each country, as shown in the image below:
Example #3
Now, we will find the Total Sale if the unit sold is greater than or equal to 2000 (>=2000)
Step 1: Write Total Sales in cell A26
Step 2: Place the cursor in cell F26 and enter the formula,
=SUMIF(C7:C24,”>=2000″,F7:F24)
We have used the same logic as in Example #1. Still, instead of selecting the country range and specifying a country name as the criteria, we have chosen the unit sold column as the range and given the criteria as >=2000.
Step 3: Press the Enter key to get the below result
The SUMIF returns the total sales if the unit sold is >=2000 as 485195.5
Example #4
USING WILDCARD CHARACTER (*) with SUMIF
The following table shows the marks of 6 students in different subjects. We want to find the Social Sciences marks of Dsouza.
Step 1: Write the Social Sciences marks of Dsouza in cell G6
From the above table, we want to find the Social Sciences marks of Dsouza. However, we have not mentioned whether it is Alexa Dsouza or Ava Dsouza. As a result, we must add the value attributed to each Dsouza using the wildcard character Asterisk (*).
Step 2: Place the cursor in cell G7 and enter the formula,
=SUMIF(B7:B12,”*Dsouza*”,C7:C12)
Note: In Microsoft Excel, we use wildcard characters asterisk (*) mark and a question mark (?) more commonly.
Step 3: Press the Enter key to get the below result
The SUMIF function returns the total marks for Dsouza as 101.
Explanation: We have written “*Dsouza*” instead of “Dsouza,” where the asterisk (*) searches for specific characters. Thus, it treats both Alexa Dsouza and Ava Dsouza as one and does the summation of Social Sciences marks for both students.
Sumif in Excel with Multiple Criteria
In the SUMIF function, we can add two criteria in a single range, i.e., we can use SUMIF for Multiple criteria. Let us understand this with some examples.
Using SUM with SUMIF function:
Example #1
Consider the same data as Example #1. Here we want to find the Total Sales value for France and Canada.
Step 1: Write Total Sales for France and Canada in cell A26
Step 2: Place the cursor in cell F26 and enter the formula,
=SUM(SUMIF(B7:B24,{“France”,”Canada”},F7:F24))
Explanation: Firstly, we put the SUM function before adding the function SUMIFS as we want to give two criteria(conditions) in the formula.
- The first parameter of SUMIF is criteria_range, i.e., B7:B24.
- We will enter the names of desired countries as the second parameter. Since we are giving multiple criteria, we first need to enter curly brackets {} to include it.
- Inside the curly brackets {}, we have to specify the desired country names in double quotes.
- The last part is sum_range, i.e., F7:F24.
Firstly, SUMIF calculates the sales for France (70,650) and then calculates the sales for Canada (4,53,964). When SUMIF returns the total sales for both countries, the SUM function will add the two sales to give the output (70,650 + 4, 53, 964) as 524613.5.
SUMIF Multiple Criteria with OR Logic:
Example #2
The table below displays a list of fruits, their farm owners, and the units grown per quarter. We want to know how many fruits the farm of Mathew and William has produced.
Step 1: Merge the cells G7 and G8
Step 2: Place the cursor in the merged cell and enter the formula,
=SUMIF(C7:C14,”Mathew”,D7:D14)+SUMIF(C7:C14,”William”,D7:D14)
Step 3: Press the Enter key to get the below result
Sum with multiple AND criteria
Example #3
The table below shows office equipment sold to respective companies with the sales made in January 2023. We want to calculate the total sales of Monitor by Zainsys Pvt Ltd.
Step 1: Write Total Sales in cell E8
Step 2: Place the cursor in cell F8 and enter the formula,
=SUM((A7:A13=”Monitor”)*(B7:B13=”Zainsys Pvt Ltd”)*(C7:C13))
Step 3: Press the Enter key to get the result shown below
Difference between SUM vs. SUMIF in Excel
- The function SUM adds up multiple values, while the SUMIF function adds up multiple values which meet given criteria
- The syntax for the SUM function is
=SUM(number1, [number2],…])
- The syntax for SUMIF is
=SUMIF(range, criteria, [sum_range])
Difference between VLOOKUP and SUMIF in Excel
- The VLOOKUP is a function in excel that works as a search function.
- It can search for a particular value across a given range of columns.
- Hence, vlookup is not a math function like SUMIF in excel
- The syntax for VLOOKUP is
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
What is Nested SUMIF in Excel?
- A Nested SUMIF function sums up multiple values that meet multiple given criteria
- We can write Nested SUMIF as SUMIFS in Excel.
- A Nested SUMIF is also known as a Nested Loop.
- The syntax for Nested SUMIF or SUMIFS is
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)
Frequently Asked Questions (FAQs)
Q1) What does the SUMIF function of MS Excel do?
Answer: The SUMIF function in ms excel is used to add the values of a range that corresponds to a particular value in a different range. The SUMIF is an additional function that checks the given criteria before adding the values.
Q2) How to apply the SUMIF formula in excel?
To apply the SUMIF formula in Excel, select a cell, type =SUMIF, and double-click the SUMIF option. Then we must write the first cell range that will work as the conditions and type a comma. Next, we must mention the criteria and then type a comma. Finally, we must add the cell range that we want to add.
Refer to the image below to understand the steps in a better way.
Q3) How to use the SUMIF function in excel with multiple sheets?
Answer: We can use the SUMIF function in Excel to perform calculations across multiple sheets using SUMIFS, SUMPRODUCT, and INDIRECT functions. The syntax for the same is-
SUMPRODUCT(SUMIFS(INDIRECT(“‘”&sheet_range”’!”&”sum_range),INDIRECT(“‘“&sheet_range&”’!”&”range”),data))
Q4) How to find SUMIF in Excel?
Answer: We can find SUMIF in Excel by simply writing =SUMIF in the formula bar or typing it directly in the cell where we want to store the value of the calculation.
The below image shows the two options where you can find the function SUMIF in Excel.
Q5) When do we use SUMIF in Excel?
Answer: We use SUMIF in Excel to add those values of a data range that meet certain criteria. We can use SUMIF to add values with multiple criteria by combining other functions such as SUMIFS and SUMPRODUCT.
SUMIF Function in Excel Video
Recommended Articles
The article has been a guide to SUMIF in Excel. Here, we discuss the SUMIF Formula and how to use SUMIF Function along with excel examples and downloadable excel templates. You may also look at these useful functions in excel-
- Use of INDIRECT Function in Excel
- How to Use FV Function in Excel?
- AND Function in Excel
- Excel PMT Function
Popular Course in this category
Related Courses