Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
In Excel for Mac 2011 Essential Training, author Curt Frye gives a comprehensive overview of Excel, the full-featured spreadsheet software from Microsoft. The course covers key skills such as manipulating workbook and cell data, using functions, automating actions, printing worksheets, and collaborating with others. Exercise files accompany the course.
When you keep your data in an Excel table or a list, you'll often find that you want to summarize your data based on one or more criteria. For example, you might want to find your total sales in the State of California and then narrow the result by finding the total sales of garlic-infused olive oil in California. You can make these sorts of calculations using conditional functions, such as SUMIF, COUNTIF, and AVERAGEIF. I will start out by showing you the SUMIF function. SUMIF, as the name implies, finds the sum of a series of data that meets a particular criteria, and you create a formula using the SUMIF function by typing equal and then SUMIF.
There are other functions that I will talk about in a second--SUMIFs lets you use multiple criteria--but I'm going to start just using the basic SUMIF. So I will type a left parenthesis, and now I can start typing in the information the formula needs. The first thing I need type in is the range, and this is the range that Excel is going to be examining to see whether it meets the criteria or not. In this cell, I want to find the total sales for California, as is indicated by my little header there.
So I will type in the range that contains my states. So I have B2 to B17. I will type a comma, and now I need to enter in my criteria. This is the rule that Excel will use to determine whether another cell in this particular row should be used as part of the formula, so, that criteria. And one in idiosyncrasy of the SUMIF and other related functions is that you need to type the criteria inside a double quote and an equal California, and close the double quotes and then type a comma, and the last argument for this formula is the sum range.
If I were looking for numerical values, then I probably would have examined column C2 through C17. Those are the values that I want to add if they are in the same row as a cell in B2 through B17 that contains the value California. So in this case, even though the argument is optional--that's what these square brackets around the name indicate--I do want to use it. So, I need to type in C2 through C17. So again, what this formula is going to do, it is going to look in cells B2 through B17.
If they contain the word "California", they're going to add the total from cell C2 through C17 that's in the same row as the value where they found California in column B. So, for example, B2 and C2, B6 and C6, B10, C10, and B14 and C14. When I press Return, Excel finds the total. Now, let's do another example using SUMIF, and this time let's examine the Revenue column, and use that as our criteria, and this way we won't have to have a second sum range.
So let me show you how to do that. I will type =SUMIF, left parenthesis, and now I'm going to be examining cells C2 through C17, C2 through C17. Type a comma. My criteria--which again goes in double quotes--is trying to find the total of revenues where those revenues were over $700,000. So my criteria is greater than 700,000.
Don't type a comma as part of this number, or Excel won't be able to read it. So just type it in. If it helps, you can type in the number with commas, make sure it's right, and then delete them. Now I am going to close the criteria with a second double quote, and now because I'm examining the values in C2 through C17 and those are the values that we're going to be adding using the SUMIF function, I don't need to add a separate sum range. I could. I could say C2 through C17 again, but I don't need to. So I'll just type a right parenthesis, hit Return, and I get the total.
Now as I promised before, I'm going to show you how to use a SUMIFs function and create a formula that calculates values based on two or more criteria. To do that, I will type =sumifs-- and again the s indicates plural--left parenthesis, and now I am going to type in the sum range. This is the set of values that Excel adds to find the value for the formula. In SUMIFs, it goes at the beginning. So the sum range is the same as last time--C2 through C17--and my first criteria is to check for sales in the State of Oregon, and those are found in B2 through B17, and Excel highlights them so I can visually check to make sure I got the right one.
Press a comma, type my criteria, "=Oregon and a comma, and now I can create my second criterion, which is for the year before 2009. So my criteria will be less than-- excuse me, I need to type in the range first. So that's A2 through A17, comma, and now the criteria, and that is before 2009 or less than 2009. Close double quotes. Everything looks good. So I will type my right parenthesis, and there you see the value.
In addition to SUMIF and SUMIFs, there are related functions that can count and find averages. I encourage you to look through the formulas in Excel, find those, and use them in your own work.
Find answers to the most frequently asked questions about Excel for Mac 2011 Essential Training.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
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.