Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
I want to show you a couple functions you can use that will let you add numbers or average numbers based on a condition. To do that, we use the SUMIF and the AVERAGEIF functions. First, let's take a look at what's happening in the worksheet. We see we have all these people and it's sorted by last name. People who are in different departments, they are of different states, we have what hours they put in, and we have the rate that they get paid per hour. So we are going to use the SUMIF and AVERAGEIF functions. So, the syntax of the SUMIF function.
We say first find where the states are. That's going to be down Column C. That's going to be the whole collection of those cells. Once we determine where the states are, we pick out which state we want. So, what we are going to do is we are going to find out of everybody who works in New Jersey, those people pick out their hours and add them. Once, we find out who they are and what state they're in and we could find out what their hours are. We want to cherry pick those hours and add them up. Now, we also wanted to an average and we are going to find out let's say for Vermont and for California, what is the average rate.
So, again we'll pick up all the states, we'll pick out Vermont, we'll pick out California. Then we will take for those particular people, we'll cherry pick their rate to average. So, let's scroll to the bottom. We'll start with New Jersey. So we are going to say =sumif, open up the parenthesis, right. Every function has a set of parenthesis. So first thing we have to do is find the range of states. So, let's scroll at the top and we'll choose the first state and you can just drag down to the last one over here. So, we could see the range is C5 to C36.
That's where all the states are. Well, that's a first argument, so we'll type a comma. Now, that we know where the states are, we want to -pick New Jersey. So pen up double quotes, type in NJ. Uppercase, lowercase, it doesn't matter. Close the double quote. That's the second argument. So, we type in a comma. Now, the third argument is what do we want to add? What is that range to add? So, let's go up here and we are going to add the hours. So we can scroll from the first one down to the last one and that's it. I'll press Ctrl+Enter.
Now, we can see the total hours for the people in New Jersey are 481.4. Let's do one more for practice. We'll do it for Pennsylvania. So, let's go here to D39, type =sumif, open up the parenthesis. The same thing, we want the same range of states. This time instead of dragging maybe this will be a little easier. Click the first one here and scroll down. I am just using the rolling wheel on the mouse and Shift+Click the last one. That is hold the Shift key down and click the last one. You might find that a little easier. So, it's the same range, type in comma, and we want Pennsylvania.
So, open up the double quotes and close the double quotes. That's the second argument so you type a comma. Well, again what do we want to sum? We want to sum the hours. So, we'll do like we did last time. Click the first hour, scroll down, I will use the rolling wheel, hold the Shift key down and click the last one. Again, we can see there is that range that we want to add and Enter or Ctrl+Enter. Now, we can see the total hours for Pennsylvania we've cherry-picked out is 221.50 hours. So now let's find the average rate.
We want the average rate. We will start with Vermont. So, click here in E41 and we'll say =averageif, open up the parenthesis and same thing, same range of states. Click the first state, scroll down, Shift+Click the last state and type in a comma. Now, we want to pick out which they do we want. So, open the double quotes for Vermont. Close the double quotes. That's the second condition so type a comma. What do we want to average? Well, we want to average the rate.
So, click the first rate, scroll down, and Shift+Click the last rate. Then press Enter or Ctrl+Enter. Now, we could see the average rate for Vermont is $25.17. So, that's how the SUMIF and the AVERAGEIF functions are very powerful tools to let you get the sum that you want and the average that you want by cherry-picking values. I think it's pretty cool.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 98511 Viewers
80 Video lessons · 141524 Viewers
59 Video lessons · 59874 Viewers
52 Video lessons · 73071 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.