SumIf

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…

Resume Transcript Auto-Scroll

Author

Released

1/31/2007

Like the other applications in Microsoft Office 2007, Excel 2007 boasts upgraded features and a brand-new look. In Excel 2007 Essential Training , instructor Lorna A. Daly introduces the new version in detail. The training begins with the essentials of using the program, including how and why to use a spreadsheet, how to set up and modify worksheets, and how to import and export data. Lorna then moves on to teach more advanced features, such as working with functions and macros. Exercise files accompany the tutorials.

Skill Level Beginner

5h 13m

Duration

3,142,732

Views

Show MoreShow Less

Q: When trying to apply the techniques from the “Relative and absolute referencing” video to a worksheet other than the exercise file included with the title, the formulas did not work for the entire worksheet. The formulas would only work when going through the worksheet row by row. What could be causing this to happen?

A: When trying to apply formulas to a whole workshee, here is a tip to try:

If you want to always refer to the same cell then use an absolute reference. For example, always pulling the value from cell A3 would be referenced as $A$3. This will never change no matter where you copy it to in the spreadsheet.

If you want to reuse the same formula, but with values in different cells, use the relative reference, A3. This way formula =A3*B3 will become =A4*B4 as you copy it down a column.

Q: In the chapter 7 video "Sorting and Grouping" at approximately 4:05, the author says to go to cell 5 on the worksheet and click on Subtotal to subtotal the grouping. My screen will not allow me to click on the Subtotal option at the top of the page. Is this an issue with my version of Excel?

A: It seems that there is an error in the instructions in this video. The video should have instructed users to do the subtotaling first, then create the table.