How to use AGGREGATE for MIN IF Formulas in Excel (Part 3 of 5)

In this video you will learn how to use the AGGREGATE function to do a MIN IF calculation in Excel. This formula will find the minimum value in a column based on matching criteria from another column.

This is the third 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 explain a technique for using the AGGREGATE function to calculate a MIN IF. This solution does NOT require an array formula. We do NOT have to press Ctrl+Shift+Enter when entering the formula in Excel.

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.

In the next video I explain how to use the MINIFS function. For that formula we do NOT need to use the Ctrl+Shift+Enter array formula.

Additional links:

00:00 Introduction
00:49 Aggregate Function
09:14 Outro

[vijaysahal]

Very nice very useful information 👍👍👍👍

[bczaru]

Generally when using vlookup the data should be sorted so you can see if there are any duplicates. I suppose if you are using a timestamp the chance of encountering this would be miniscule. However, I could see this happening if this formula was used elsewhere. When I sorted the dates, there were two people for January 1, but with different time stamps. I changed them to be the same. When I changed the ranking to 2, Teresa Snyder came up again. When I changed to 3, Jimmy Bailey came up. It would seem at no time would Martin Gilbert come up. This to me would be strange, because when you think about it, if two items evaluated to 1, then Bailey, should have come up as 2.

[ExceliAdam]

AGGREGATE is cool because it don't require CSE

[baphoo]

Dear Jon, even trying every route, still I couldn't solve the problem. Shall I contact you in private. Kind regards, Munevver

[te.a]

How do I find who made the last sale in each month?