Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
The owners of EatCake have just finished their year-end Inventory, and had everyone put their accounts into a spreadsheet, however, no one did a tally of each Item Type. So now they either print off the pages in the worksheet and count them by hand or they use one of the advanced functions Excel offers to do the work for them. This movie's going to cover a lot of the concepts we've reviewed so far, so you may want to watch it a few times. Open EatCake 18 to follow along. We're going to add up the inventory items for three areas: Pastry, Buttercream and Sugar Free.
And you'll see that we already have an example here in our Pastry area, it's saying that there are 7 current inventory items under the Pastry item type. Let's just see if that's right. I've got 2 here under Pastry, and if I scroll through the visible area, I see another Pastry with the inventory of 5. The total of those two is 7, so I know in my formula is calculating correctly. Now, let's just take a look at this formula before we go in and learn how to create it up here in the Formula bar.
Basically, we're using the SUMIF function, and we're telling it to take a look at a particular area for a certain type, and then go to a particular column, and add up and everything that you find in that column that matches under this criteria. So how do we create something like this? How do we get the power of the Excel application to work with us? First of all, we start off in the cell where we want the answer to go. And then we go up to the Formulas tab.
Under the Formulas tab, we go to the Function Library, and in the Function Library the SUMIF function is contained under the Math & Trig grouping. So I select that, and I go down until I find SUM formulas, and I select SUMIF. Then I get a dialog box coming up. And this dialog box prompts me to fill in the missing pieces. First of all, it's asking me for a Range. And the range is the area of cells that you want evaluated.
So in Excel, we start with identifying the very first cell we are interested in, which is, in this case, B2. We then tell it to look through a particular section of cells, and so the end of the cell, we want it to look in column B, and we're going to just look at the area on the screen here, so the final row we can see is B25. So we're saying, "Go to the information that's contained in column B, starting at row 2 an ending at row 25." Then we're going to identify the criteria, and the criteria is the condition, or criteria in the form of a number, expression, or text that defines the cells that will be added together.
So basically it's saying, "What do you want me to find in column B?" Well in this case, I want to find the word buttercream, so I type in buttercream, making sure that my spelling is correct. It's not case sensitive. But the spelling, it will look for to be an exact match. Finally, I identify what column I want to find the information that I have to add up. I could add up current cost, but that's not what I'm interested in. I'm interested in the inventory, and the inventory is included in column E. So, sum up anything that's in E2 to E25.
Again, we're looking at the same area, the same range, in just a different column this time. It gives me an idea of what my answer's going to be. Here it's coming up to a 3, and then let's set this particular function in place and see if that makes sense. So it's telling me that it's looked through the range and it's come up with the number 3. So if I look for buttercream, I find 1 buttercream, and I see I have 3 in the inventory. Go back through my list. It's not finding this blue buttercream because it's not an exact match, and I scroll through and I see I have no other buttercream, So the formula is correct.
Once you feel comfortable working with Excel functions, you can save yourself hours of time doing mathematical calculations, and create some pretty powerful analysis. Now speaking of powerful, let's explore the Lookup function in our next movie.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 91242 Viewers
80 Video lessons · 138207 Viewers
59 Video lessons · 57036 Viewers
52 Video lessons · 70676 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.