Excel - Simple Explanation Of SUMIF with Example In Excel - Episode 1402
2,217 views · Published 11 May 2011 · 4:07 · Indexed 12 November 2026
Channel: MrExcel.com · 2011 · Education
Microsoft Excel Tutorial: Explaining SUMIF function in Excel. Welcome back to the MrExcel podcast. In this episode, we will be discussing the SUMIF function in Excel. This question was sent in by one of our listeners who was having trouble using SUMIF to calculate a weighted average unit price. Upon looking at their spreadsheet, it was clear that they had a basic understanding of SUMIF, but were struggling with the total price calculation. This is a common issue that arises when someone who is not familiar with SUMIF inherits a spreadsheet from someone else. So, let's dive into the SUMIF function and understand how it works. First, let's take a look at the SUMIF formula. It starts with the range of cells that we want to check for a certain criteria. In this case, we are checking column A for the company name "Microsoft". Then, we specify the criteria we are looking for, which is "Microsoft" in cell E2. Finally, we tell Excel which cells to add up if the criteria is met, in this case, it is column B. This formula will add up all the values in column B that correspond to the company name "Microsoft". We can then copy this formula and change the criteria to calculate the total for other companies as well. Now, let's take a look at how we can use SUMIF to calculate a weighted average unit price. We start by using the SUMIF formula to calculate the total revenue, just like we did before. Then, we divide this total by the quantity to get the average unit price. This may seem like a long formula, but once you understand how SUMIF works, it becomes much easier to use. And the best part is, if we add new data, the formula will automatically update without having to retype it. To make things even easier, we can use the F4 key to make our cell references absolute. This means that when we copy the formula, the cell references will not change. This is especially useful when working with large datasets. So, next time you need to calculate a weighted average unit price, remember to use the SUMIF function and make your life a little easier. Thank you for tuning in to this episode of the MrExcel podcast. We hope you found this explanation of SUMIF helpful. Don't forget to subscribe to our channel for more Excel tips and tricks. See you next time! Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ Table of Contents: (00:00) SUMIF explained (00:15) Goal: Get Quantity and Average Unit Price for Chosen Company (00:25) Need for Weighted Average Unit Price (00:35) Explanation of SUMIF Function (01:01) Example of SUMIF Formula (01:14) Copying and Pasting Formula (01:33) Explanation of Formula for Average Unit Price (01:52) Advantages of Using Formula (02:02) Simple Division for Average Calculation (02:15) Building Formula for Average Unit Price (02:25) Pointing to Data on Another Worksheet (02:49) Dividing by Quantity (03:07) Testing Formula (03:48) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #excelformula #excelformulasandfunctions This video answers these common search terms: simple explanation of SUMIF in excel how to do sumif formula in excel what is sumif used for in excel what is a sumif statement in excel how to sumif in excel using a formula how use sumif function in excel with example how to create sumif formulas in excel excel how to do sumif Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153080/ Today, in Episode #1402, Bill takes a look at Calculating a Weighted Average using two (2) SUMIF Functions. This Formula will need to reference Data on another worksheet as well. Learn about "SUMIF"!
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920