How to Use AGGREGATE for MIN IF Formula with Multiple Criteria (Part 5 of 5)

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.

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:

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

[velavanjobtyping]

i see the variable aggregate videos, but yurs video is easy to understand

[iamriza]

Wow! You just solved my problem. Thank you so much for this video. Amazing!

[somshekar]

Your simply awesome, great content buddy

[Allmytravelz]

Always enjoy your vids. I learn a lot of neat functions

[moisestatis]

Great video, thanks! Would you have one with Aggregate suming with multiple criterias?

[arhome]

Well done Jon thank you for this video. I can get this technique to work with the functions that have a k argument but for some reason I can't get it to work for the ones that don't. For example, Average, Median, Max and Min (without using Small & Large or Percentile). My criteria are the same when I try with and without the k argument but so far, I can only get it to work on the functions with the k argument and there is no substitution function for Average that has a k argument. Yes, I’m removing the k argument from the formula when I try but I still get a #VALUE error. Any ideas?

[anhnhatnguyet]

There is a video that instructs to find multiple conditions (2 or 3 or more conditions), returns many results shown on 1 column, or 1 row. I have only been able to do one condition detection now

[subasalok]

Thank you very much for your useful videos sir. This formula works perfectly for my excel, But, it is too slow while operating this formula. is there any solution?

[crazyforcs]

Great video series! I have a question about why using Index and Match isn't working for me. This is my function:

=MIN(INDEX(tblShirts[Qty], MATCH($F$5, tblShirts[Color], 0), MATCH($G$5, tblShirts[Month], 0)))

I get 712 instead of 630. When I use the formula evaluation tool, it shows that the second Match function is able to find only one "Jan" value which is very weird.

I'm new to excel so sorry for the dumb question.

Oh and if I switch the places of the two Match functions, so using the Month as the first criteria, I get a #REF! error.

[meenakshisundaramjeyaraman]

Good. Thanks for this. Can you please share this excel sheet

[v.s.]

I tried to use this formula MIN(IF(AND($G$5=tblShirts[Month], $F$5=tblShirts[Color]), tblShirts[Qty])) however it returns 0 and I don't get why it is not working.

[MrFill]

Hi. Help

Here is a challenge. Every month has weeks that have values corresponding to each week. I need to return the first value of the month!!! How do I do it?

[لاالهالاالله-تشك]

Hey besties, greetings and after

Upload for your presence a file that needs an equation in the field of the previous and current quantity in Sheet 2

The file consists of sheet 1 and sheet 2

The data in Sheet 2 is taken from Sheet 1, except for the business statement in Sheet 2, in which I write the items and work on them in Sheet 1

In the table, find the last value of a specific name from a previous quantity and a current quantity, so you will automatically download it

Both past and present quantity

But if I need to do a filter in Sheet 1 on Gary 10

You need everything that happened in the filter to appear in Sheet 2

It shows me a previous and current amount based on the filter

When I remove the filter, it shows me the last values ​​in all equations

Thank you very much for your presence

[carolrice]

i don't understand how you know to use the division or mulitplication symbols