Learn how to use SUMIF Excel Function

The SUMIF function is a built-in function in Excel that is categorized as a Math/Trig Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the SUMIF function can be entered as part of a formula in a cell of a worksheet. You can try the SUMIF function to add numbers in a range based on multiple criteria.

Related Videos: Learn More Such Excel Functions: Excel Function Videos 

To learn how to use SUMIF, we also have to know what is SUMIF and when can we use it?

What is SUMIF Function?

The Excel SUMIF function finds the values in a supplied array, that satisfy a given criteria, and returns the sum of the corresponding values in a second supplied array.

When to use SUMIF?

SUMIF Function is used to add the specified cells based on the single or multiple supplied criteria. Sometimes we require conditional sum in Excel then we can use this function to add all numbers in a particular given range. SUMIF function is based on the three parameters- range, criteria, and sum_range. Every parameter has own functionality and also depends on SUMIF multiple criteria.

Read More: List Of Excel Keyboard Shortcuts with Formula For Beginner

Function Syntax

The SUMIF Function in Excel has the following syntax:

=SUMIF(range, criteria, sum_range)

Parameters

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

criteria: The criteria used to determine which cells to add.

sum_range: Optional. It is the range of cells to sum together. If this parameter is omitted, it uses range as the sum_range.

Now, let see how to use SUMIF in excel with a Case Study.

sumif function in excel

Case Study: Consider that we have a shop and 5 people have ordered goods from us. Some people have paid us, but some haven’t. We want to calculate how much in total has been paid to us and how much is still owed.

Read More: 5# Powerful tricks to format cells in Excel

We can do it using SUMIF.

Let’s go through the following steps.

Step 1: Open Excel sheet and from Row 1, create three columns named Customer, Product Price and Payment Status. Enter data under these 3 columns as shown in the above picture. Here we will calculate:

  • Total Paid Price (Cell A10)
  • Total Due Price (Cell A11)
  • Sum of product prices that sold over $100 (Cell A12)

sumif-for-total-dueIn cells B10, B11 and B12, we’ll use a SumIF function to work out Total Paid Price, Total Due Price and Sum of product prices that sold over $100. Here’s the SumIF function again:

SUMIF(range, criteria, sum_range)

Calculate Total Paid Price

The range of cells that we want to check are True and False values in the C column. The criteria is whether they have paid (True) and the Sum_Range is what we want to add up (in Column B).

Step 2: In cell B10, enter the following formula:

=SUMIF(C3:C7, TRUE, B3:B7)

Step 3: Press Enter. Excel should give you the answer 265 in cell B10. In the formula we told SumIF to first check the values in the cells C3 to C7 (range). Then we said look for a value of TRUE (criteria). So we wanted the values in the B column adding up, if a criteria of TRUE was indeed found (sum_range). Finally, we got the result 265 that has been paid in total.

sumif function in excel with exampleCalculate Total Due Price

Step 4: In cell B11, enter the following formula:

=SUMIF(C3:C7, FALSE, B3:B7)

result-for-total-dueStep 5: Press ENTER. Excel should give you the answer 313 in cell B11. In the formula we told SumIF to first check the values in the cells C3 to C7 (range). Then we said look for a value of FALSE (criteria). So we wanted the values in the B column adding up, if a criteria of FALSE was indeed found (sum_range). Finally, we got the result 313 is Total Due Price.

sumif-functionCalculate Sum of product prices that sold over $100

Step 6: In cell B12, enter the following formula:

=SUMIF(B3:B7,”>100″)

result-of-sumif-functionStep 7: Press ENTER. Excel should give you the answer 544 in cell B12. In the formula we told SumIF to first check the values in the cells B3 to B7 (range). Then we said look for a Price value higher than $100. So we wanted the values in the B column adding up, if a criteria of >100 was indeed found (sum_range). Finally, we got the result 544 is Sum of product prices that sold over $100.

Tips: There are many ways to find out a particular Sum. Say, if you want calculate the total price sold out to a particular customer named Joni, then just shift your range of that formula from B3:B7 to A3:A7 and type criteria as the name of customer Joni then select the sum_range B3:B7. So, the formula would be:

=SUMIF(A3:A7, “Joni”, B3:B7)

Now You have successfully learned how to use SUMIF Formula in Excel

Related Tutorials

Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018
OFFSET Function in Excel
July 6, 2018
Strikethrough Shortcut in Excel & Word
July 4, 2018
INDIRECT Function with SUM, MAX, MIN & Independent Cell Value
June 29, 2018
Pivot Table Slicers In Excel
June 12, 2018
How to Split Cells in Excel using Text to Column
June 7, 2018
How to Wrap Text in Excel Automatically and Manually
June 6, 2018
How to Hide/Unhide Column in Excel
June 5, 2018
Highlight row based on cell value
June 4, 2018
Learn how to remove blank cells in Excel
June 3, 2018
How to Group Numbers, Dates & Text in Pivot table in Excel
June 1, 2018
5 Powerful Tricks to Format cells in Excel
May 31, 2018
Insert a Picture into a Cell in Excel
May 25, 2018
What is ISFORMULA Function and FORMULATEXT Function
May 21, 2018
How to Use SUBSTITUTE Function
May 21, 2018
Excel Quartile Function in Excel
May 8, 2018
How to use the Excel PERCENTILE function
May 7, 2018
Insert or Type degree symbol in Excel with Autocorrect Feature
May 7, 2018

Leave a Reply

25% Discount
No prize
All Courses at $200
Almost!
10% Discount
Free Ebook
No Prize
No luck today
Almost!
50% Discount
No prize
80% Discount
Get a chance to boost your knowledge!
Use this coupon code for any course that you wish for. 
Our in-house rules:
  • You can choose any course & redeem coupon
  • If you find any difficulty, mail us on [email protected]
  • Wheel Spin will end soon
  • Coupon code can be applied within 2 days.