Two new functions in the Feb 2016 release of Office 365 are MAXIFS and MINIFS. These join the existing SUMIFS, COUNTIF, and AVERAGEIFS to let you find items that meet multiple criteria.
Transcript of the video:
Learn Excel from MrExcel podcast, episode 1966 - MAXIFS and MINIFS!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well two more functions, that just came out in the February 2016 release of Office 365, are MAXIFS, plural, and MINIFS.
This kind of reminds me of the new SUMIFS, that was introduced a few versions ago.
So we have a database here, where we have a Rep, a Class, and then a Rating, if we want to figure out the highest rating, that Andy got for the Pivot Table class, we would use =MAXIFS, plural, now just like SUMIFS, and COUNTIFS, and AVERAGEIFS, you want to specify the numeric field first, so I'll choose, then I'll press f4 to lock that down, and then the first criteria range, the first criteria range is, we're going to look through all of the sales reps over in column A, press F4, and see if that's equal to Andy.
Now right here, I'm going to press F4 three times, to make sure that we're always pointing at column E, but the row isn't changing from 4 to 5 to 6.
Next up, we'll look through all of the classes in column B, F4, see if that's equal to Pivot Table, and here I'll press F4 twice, to lock it down to just the row, and we'll see that Andy's highest score was a 5, and Bett 4, Charlie 2, and then 3.
Alright, so that's the new MAXIFS function, the MINIFS function is going to be very similar, copy it down here, I'll do a CTRL+H, change every occurrence of MAX to MIN, Replace All, Close.
We'll take a look - exact same thing, but here's looking for the MIN instead of the MAX.
Alright, so that is a total of 6 new functions that were added in February 2016 to Office 365.
Alright, our next video is going to talk about, how you can get these functions if you have Office 365, and it's a lot more confusing than you think.
Use that "i" icon up there in the top-right hand corner to jump to that video.
Alright hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well two more functions, that just came out in the February 2016 release of Office 365, are MAXIFS, plural, and MINIFS.
This kind of reminds me of the new SUMIFS, that was introduced a few versions ago.
So we have a database here, where we have a Rep, a Class, and then a Rating, if we want to figure out the highest rating, that Andy got for the Pivot Table class, we would use =MAXIFS, plural, now just like SUMIFS, and COUNTIFS, and AVERAGEIFS, you want to specify the numeric field first, so I'll choose, then I'll press f4 to lock that down, and then the first criteria range, the first criteria range is, we're going to look through all of the sales reps over in column A, press F4, and see if that's equal to Andy.
Now right here, I'm going to press F4 three times, to make sure that we're always pointing at column E, but the row isn't changing from 4 to 5 to 6.
Next up, we'll look through all of the classes in column B, F4, see if that's equal to Pivot Table, and here I'll press F4 twice, to lock it down to just the row, and we'll see that Andy's highest score was a 5, and Bett 4, Charlie 2, and then 3.
Alright, so that's the new MAXIFS function, the MINIFS function is going to be very similar, copy it down here, I'll do a CTRL+H, change every occurrence of MAX to MIN, Replace All, Close.
We'll take a look - exact same thing, but here's looking for the MIN instead of the MAX.
Alright, so that is a total of 6 new functions that were added in February 2016 to Office 365.
Alright, our next video is going to talk about, how you can get these functions if you have Office 365, and it's a lot more confusing than you think.
Use that "i" icon up there in the top-right hand corner to jump to that video.
Alright hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
