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

Use MAXIFS() and MINIFS() in nested functions

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

Start my 1-month free trial

Use MAXIFS() and MINIFS() in nested functions

“

- [Instructor] MAXIFS and MINIFS are two new functions in Excel 2019. Now you have probably used max and min functions before but these are so fantastic because we can now find the largest value in a range based on multiple sets of criteria and of course, we can also find the smallest value in a range, also based on multiple sets of criteria. Let's take a look at our MAXIFS, MINIFS workbook. We will first find the largest salary in the Account Management department in the Dallas location for employees that have full-time status. Equals MAXIFS. And first we're looking for our max range and that will be salary. I'll click in H4. Control, Shift and down arrow will select that entire range. I'll type a comma and now we're ready for our first criteria range. So we're looking for employees in the Account Management Department. C4 and a comma and then in double quotation marks I'll type Account Management, comma. Our second set of criteria is location. Comma and we're looking for employees in Dallas. And I'll type a comma and we're ready to enter our third set of criteria, which is status. I'll type a comma and type in double quotation marks Full Time, close our parenthesis and hit enter. And there we have our largest salary amount in the Account Management Department in Dallas for full-time employees. Okay, let's take a look at our next question. What's the earliest date of hire for the Los Angeles location for employees who have full-time status? Okay, equals MINIFS. Our range will be the Date of Hire. Don't forget about that Control, Shift, down arrow that quickly selects the range of information. And I'll type a comma. Our first criteria is location. I'll type a comma and in quotation marks I'll type Los Angeles. Close my quotation marks and type a comma. Our second set of criteria is full-time status. I'll click in D4. Control, Shift, down arrow to select that range. Comma and in double quotation marks, I'll type Full Time, end my quotation marks, close my parenthesis and hit enter. And here's the date of the full-time status employee in Los Angeles who has been there the longest. February 3, 1998. So, that's how you can use these two great new functions available to you in Excel 2019 and Office 365 that will let you use several sets of criteria to find both the largest and the smallest value in a range.

Contents