Video: Using SUMIF and AVERAGEIF

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.

In Excel 2010 Essential Training, Bob Flisser demonstrates the core features and tools in Excel 2010. The course introduces key Excel skills, shows how to utilize these skills with in-depth tutorials on Excel functions and spreadsheet formatting. It also covers prepping documents for printing, working with large worksheets and workbooks, collaborating with others, using Excel as a database, analyzing data, charting, and automating and customizing Excel. Exercise files are included with the course.

Using SUMIF and AVERAGEIF

I want to show you a couple functions you can use that will let you add numbersor 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 havewhat 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 whoworks 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 outwhat 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 forVermont 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 aset 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 justdrag 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 doone 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 andsame 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 tolet you get the sum that you want and the average that you want bycherry-picking values.I think it's pretty cool.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.

Already a member ?

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships.
Learn more

Upgrade to our Annual Premium Membership today and get even more value from your lynda.com subscription:

“In a way, I feel like you are rooting for me. Like you are really invested in my experience, and want me to get as much out of these courses as possible this is the best place to start on your journey to learning new material.”— Nadine H.

Thanks for signing up.

We’ll send you a confirmation email shortly.

Sign up and receive emails about lynda.com and our online training library:

new course releases

newsletter

general communications

special notices

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

new course releases

newsletter

general communications

special notices

Here’s our privacy policy with more details about how we handle your information.