Скачать или смотреть How to Use AGGREGATE for MIN IF Formula with Multiple Criteria (Part 5 of 5)

Sign up for our Excel webinar, times added weekly: https://www.excelcampus.com/blueprint...

In this video you will learn how to use the AGGREGATE function to do a MIN IF calculation based on multiple criteria in Excel. This formula will find the minimum value in a column based on matching criteria from two or more other columns.
Download the file: https://www.excelcampus.com/functions...

This is the fifth video in a series on solutions to the Formula Challenge to find the first sales transaction for each month of the year. There were over 100 solutions submitted on the blog page, so I created a series of videos to explain the most popular solutions.

In this video I expand on the technique for using AGGREGATE with multiple criteria. I first explain a simple example, then show how we can apply this to our sales competition to return the first sales person for the month for each region.

We use the SMALL function in AGGREGATE to return the 1st or smallest value. The SMALL function has an optional [k] argument that allows us to return the 2nd, 3rd, etc. smallest value. This makes the formula a bit more flexible than the other solutions.

AGGREGATE was introduced in Excel 2010 for Windows and is available on all versions of Excel for Windows and Mac after 2010. It's similar to the SUBTOTAL function, but provides additional functions and options for handling errors and hidden rows. It's a very versatile function and a great one to know.

Additional links:
#1 Excel Array Formulas Explained with MIN and IF Functions    • Excel Array Formulas Explained with MIN an...  
#2 MIN IF Array Formulas with VLOOKUP or INDEX MATCH    • MIN IF Array Formulas with VLOOKUP or INDE...  
#3 How to use AGGREGATE for MIN IF Formulas in Excel    • How to use AGGREGATE for MIN IF Formulas i...  
#4 How to use MINIFS to Calculate Minimum Value based on Multiple Criteria    • How to use MINIFS to Calculate Minimum Val...  

Calendar System & Dates Explained in Excel: https://www.excelcampus.com/functions...
Beginner's Guide to Excel Tables: https://www.excelcampus.com/tables/ex...
How to Turn Table Formulas On/Off: https://www.excelcampus.com/library/t...
00:00 Introduction
00:12 How to Use AGGREGATE for MIN IF Formula with Multiple Criteria
00:36 Formula Challenge
02:38 Bonus – AGGREGATE with Multiple Criteria
05:10 Outro