From the course: Cert Prep: Excel Expert - Microsoft Office Specialist for Office 2019 and Office 365

Use SUMIF(), AVERAGEIF(), SUMIFS(), and AVERAGEIFS() functions

From the course: Cert Prep: Excel Expert - Microsoft Office Specialist for Office 2019 and Office 365

Start my 1-month free trial

Use SUMIF(), AVERAGEIF(), SUMIFS(), and AVERAGEIFS() functions

“

- [Instructor] Let's take a minute to look at SUMIF(), SUMIFS(), AVERAGEIF() and AVERAGEIFS(). Now SUMIF() requires just one set of criteria and if that criteria is met it will fall within the range that we've asked Excel to sum. SUMIFS() requires two or more sets of criteria and if both of those are met it will then sum the data in the range that we've selected. AVERAGEIF() and AVERAGEIFS() works exactly the same way except of course that it averages the information instead of summing it. Let's take a look at our SUMIFS() AVERAGEIFS() workbook. Now if we just wanted to average all the salaries for all our employees, we could use equals, average and sum the salary range. But if we want to dig a little deeper to see the total salaries for our training employees we would want to use SUMIF(). We just have one set of criteria. Equals SUMIF(). And we will of course look at the department. I'll click in before and use control shift down arrow to select that range. So that's our range. I'll type a comma and let's put our criteria in. In quotation marks I'll type Training. Close my quotation marks, type in a comma and now what is the range that we want to sum? Well it's the information in column G. I'll click in G4, use control shift down arrow to select that range and hit enter. There we have to total salary amount for all of the training employees combined. Now let's take a look at the next question we have. What's the total salary for full time training employees? Well we have two sets of criteria and yep, you guessed right, we're going to use SUMIFS(). Equals SUMIFS(). Which range to we want to sum? It will be the salary range. Click in G4, use control shift down arrow to select that range. Let's go back up. Type in a comma. Now we're looking for our first set of criteria which is the status, which is in column C. So it's now looking for the criteria range. Just always watch your syntax underneath so you will always know what Excel's expecting from you. So our status criteria range begins in C4. Control shift down arrow we'll select that entire range. Let's type a comma in and the criteria will be Full Time. And of course that will be in double quotation marks. Be sure and type it exactly the way it displayed in the cell. Okay, that's our first set of criteria and a range that we're looking in. Next, our next range will be the department range because we're looking for training employees. I'll click in B4. Use control shift down arrow. That's our department range. Type in a comma and then in quotation marks type in Training. I'm looking for our training employees. Now that should be all the information that Excel needs. We can close our parenthesis and hit enter. And I will go ahead and format that as currency and we now see for all our full time training employees what the total salary amount is. Average works exactly the same way. So if we want to average the years for the marketing employees, we will have it look at the E column. Specifically for the employees in the Marketing Department. Equals AVERAGEIF(). We're first looking for our range, which will be department. I'll type a comma and we're looking for Marketing. Type a comma and a range that we want to average will be in column E. I'll hit enter. And there we see the average years for our marketing employees. Now I'm going to leave the question in column L for you to complete. So try that on your own and practice using the SUMIFS() and AVERAGEIFS() on your own data in your workbooks so that you will be very comfortable with these four functions when you sit for the exam.

Contents