MAXIFS and MINIFS added to the COUNTIF/SUMIF family of functions

- [Voiceover] We've got two new functions that were added recently to Excel, MAXIFs and MINIFs. And it's worth reviewing not only what they can do, but also the companion functions that have been around for a while in Excel. Let's start with the simple function COUNTIF. An ideal function for tabulating totals out of a list. I'm looking at this list over here. We don't need to see the data to the left of it, but in column C we've got status here. There are four different kinds. I simply want to know how often full time appears in the column, or in other words how many full time people do we have? If I click on a cell and double-click its bottom edge or press Control + DownArrow.

We'll see quickly it goes down to row 742, so I've got 741 people here. Let's use the COUNTIF function. Now, instead of typing full time, I've got this in a nearby cell so we'll simply use that, =COUNTIF. And you'll see it in the list here right away. Perhaps, you could just tab it into place. We're looking in column C. By using this kind of a reference, if this list grows or shrinks, we won't have to rewrite this formula + Comma. And what are we looking for here? If I didn't have full time in a nearby cell I would now type double quote F-U-L-L space T-I-M-E double quote, but it is in a nearby cell, so I'll simply click that.

Don't need to put in the right parenthesis, so I'll press enter and we've got our answer. And double-clicking to copy this into the next three cells, we see the answers for half-time, hourly and contract so fairly straightforward there. Now, there are two companion functions. One's called SUMIF. One's called AVERAGEIF. We don't have to see them both, but they both start off a little bit differently. Let's say we focus on just AVERAGEIF now, and we'll use it here for the full time count. AVERAGEIF and its counterpart SUMIF have three arguments in the function.

Both of them begin the same way as COUNTIF. We're looking to find those entries in column C that are full time, + Comma. But when we find them we want to know, for example, the average compensation or maybe the average years of service, so I'm going to click column G. Now, when you are using entire column references, we don't have to worry about the exact addresses. However, if we were using exact addresses here, for example C2 colon C742. In order to expect a correct answer over here, we'd have to be seeing G2 colon G742.

Think of these two entries here as being in parallel. They're in parallel columns or in parallel rows. So, AVERAGEIF goes a step further as does SUMIF compared with COUNTIF. We'll get an answer here and obviously, we'd want to format that. From the home tab we could just click the comma here and that's our answer. The average compensation for the full time people in this list working off the data in column C and column G. Now in addition to COUNTIF, SUMIF and AVERAGEIF, we've got three additional functions COUNTIFS, SUMIFS, AVERAGEIFS.

Note how they all end in s. In other words, they're simply repeating the previous function names and adding an s. And think of this as being plural therefore this means we're allowing multiple critera. So for example, I might want to know the number of people who's status is full time and who's job rating is five so =COUNTIFS. Once again as we see this in the list here, we can just tab it in. Remember it's plural now, so we can look for multiple criteria.

Think of what we're about to do as existing in pairs. First pair is column C and we're looking for full time. I'll just use that cell M1 again since it has full time in place. That's one criterion you might say or a criteria pair + Comma. How 'bout another one? We're looking in column H + Comma. We're looking for the fives. Those who have a job rating of five. That's how I'd put it in. And by the way, if we were trying to pick up the fours and the fives, we can do this by putting in ">3, or possibly greater than or equal to four or whatever is clearer to you within double quotes.

That's another pair. So, how many of our full time people have a job rating of four or five? That's 181. So you can see how that works. Now, we can have other criteria pair as well here. That's one criteria pair, there's another pair. We can have many, many. Well over 100 here. Same general idea with SUMIFS and AVERAGEIFS. However, when you're using these two, surprisingly with AVERAGEIFS and with SUMIFS, we begin with what it is we're trying to tabulate. Let's say it's compensation again, column G.

And then we put in our criteria pair the way we did with COUNTIFS. So what are we looking for first of all? Let's say again, we're looking for ultimately the average compensation for all of our full time people who have a job rating of five or let's say four or more, either way. Again, column C + Comma and M1 to pick up full time. That's one criteria pair right there and we can have many, many more. The next one's going to be, like we saw earlier with job rating. Let's say this time we're just interested in those who are equal to five, so we can just put in a five like that, and if we wanted to go a step further and indicate those with certain years of service + Comma.

We could point to column E. And what are we looking for in column E? Those that are greater than or equal to 10 or something like that or simply greater than nine maybe. That would work okay. So, a bit long here but what are we saying? We are about to tabulate the average compensation out of column G but only for those records that meet these three criteria. Number one is from column C, their status must be full time, from column H, their job rating must be five and from column E their years of service must be greater than nine.

And we've got an answer there too. We could copy the format from above, simply with the right mouse button drag this down to here, copy here as formats only. You could do it that way. Now, we've got these two additional new functions just introduced, MAXIFS and MINIFS. Notice that not introduced is a function called MAXIF or MINIF. We do not have those functions, but we have these. Now, prior to the introduction of these functions, trying to come up with this answer would involve a rather complex array formula, but now we don't need these anymore.

In fact, we can think of these in the same way that we think of AVERAGEIFS and SUMIFS. So what I'm going to do here to create this I might say the following, I'd like to know the highest compensation amount for the people that meet the criteria that we saw as I created AVERAGEIFS. So I'm just going to drag this down for the moment. Make an adjustment or two. Suppose this is MAXIFS now. What are we about to say? Looking in column G, we're trying to find the highest compensation amount based on the entry in column C being equal to full time, so I'll change that to M1, and the entry in column H, job rating of five and the entry in column E being greater than nine.

So what are saying in English here? Let's find the highest compensation amount for the full time people who have a job rating of five who have been here more than nine years. What's that highest compensation amount? And there it is. Now we don't know where it is. We could do that by sorting perhaps or other techniques, but MAXIFS allows us to find that. And similarly, and I'll simply drag this downward here, then again make some adjustments. We could find the minimum amount here. We'll change that into MIN and once again I have to make an adjustment here with M2.

Make that the M1 and we're all set. The minimum amount, in other words what's the lowest compensation amount for any of our full time employees who have been here more than nine years who have a job rating of five and that's 30,175.00 and we should probably look into that. Nevertheless, we go back to this idea. Two new functions now available in Excel add to the mix and to this family. These are powerful tools to be sure and they're widely used when we're working with lists.