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

Watch on YouTube

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