Excel Magic Trick 1013: MIN IF, MAX IF, MINIF, MAXIF (6 Examples) MAX MIN calculation w 1 criteria

See how to calculation MIN and MAX calculations with one condition. See how to calculate the minimum and maximum sale for each employee using:
1. DMIN function
2. DMAX function
3. MIN and IF array Formula that requires Ctrl + Shift + Enter
4. MAX and IF array Formula that requires Ctrl + Shift + Enter
5. MIN conditional calculation with AGGREGATE Excel 2010 function with division math array operation in array argument of AGGREGATE that does not require Ctrl + Shift + Enter
6. MAX conditional calculation with AGGREGATE Excel 2010 function with multiplication math array operation in array argument of AGGREGATE that does not require Ctrl + Shift + Enter

[excelisfun]

I am glad that the videos help! It is also great to have you, Mike, and many of the other Online Excel Teammates so that we can all hang out and get better at Excel together!

[MikeDolanFliss]

The use of division to prompt DIV/0s to help out the min borders on deep magic / wizardry! You and Bill are worldwide resources. So glad to have you.

[Vicepresidente]

Very good explanation for all alternatives

[xquidditchcaptainx]

I've been binge watching your videos, because so many of them are helpful for my assignment.. Thank you soo much!! You're a life saver

[mattgohr]

This is truly incredible.  You my friend are a genius.  That Aggregate formula to skip errors is incredible.  Thanks so much for the help.

[TheOndricek]

Just what I needed! Thank you. Aggregate function is amazing!

[ExcelStrategy]

All those three solutions are great !

[mwivwqb]

Thank you so much!it was very helpful!

[RicartJon]

Thank you, really easy and as usual super insightful

[esmat]

Great vedios Mike.

How to find and extract a peak number. I'hv got 2, 5, 20, 15, 2, 30, 3, 1, 2, 1. My data order is from left to right. First I want to extract 20 at 1 place, then 30 and 2 at second and third place. Thanks a lot.

[christoslefkimiotis]

just a question, before referring to your video I tried the CSE formula MAX((A:A=criterion1)*(B:B)) and it worked fine, I did not use the if way and it worked ok, when I tried the same for the MIN it always returned 0 value, Do you have any reasonable explanation about that? In order to overcome my problem I used your if way. Your tricks in general has made me to see excel from a different point of view, thank you

[ronikumar]

How to find min value of emp1 except o
Like : emp1 has [5, 6, 9, 0, 18, 34] then find min value of emp1 except 0

[pjhdude]

I have an excel problem? Could I send you an example with the question and see if you can help?

[MrIgorCo]

Thank you for a video.  
I have a  question, if  for example I dont have all values  in my  amount column,  let s say Emp1 has  only   B2   50   and B6  150, but B4  is  blank.  How can  I write a   formula to  get  a  min  or  50,   but  not  0.  Thank you. 

[trime]

What if I want my criteria to be: _"If it includes x"_ and not _"If it fully consists of x"?_ Normally I'd use *"*"&A1&"*"* instead of simply *A1* as my criteria, but it doesn't seem to be working here.

*_This is how it looks like formula-wise:_*

Assuming B1:B3 is the range in which I find the numbers I need to find the MAX value for, and C1:C3 is the range in which I find the cell that can include my criteria, and A1 is the cell in which I can find my criteria.

I tried 2 combinations of functions to achieve this:

[elodionocesa]

I cant figure out how to do this with dates

[krn]

So when we getting MinIF? Lol. Thanks Mike

[robertosweet]

hey man, how can i contact you. I've been working on a spreadsheet and i need some help

[BIGGOODBOY]

Can someone please help me, I can’t figure out a solution to this. I need to create a top 10 list without having to pivot or update any formulas; I need this to be as automated as possible so that whenever I download new data I automatically receive the top 10 list. I will give you an example I have 5 columns: Column 1) is product and there are 4 products however I only want to look through 3 of the 4 products in the data. Column 2) is name, there are multiple names and duplicate names as there are many transactions so I would need the formula to add the matching names together to see which are the largest. Column 3) is Sales. Column 4) is whether this is national or international sale, I need to create a formula for both and one for international only. Column 5) will be whether its dollars or pounds.  So to sum it up I need to return the top 10 clients by filtering out 3 of the 4 products. I am currently using an Index Match Large formula however it looks through all 4 products instead of just 3. I tried using sumifs however I am not sure how to combine them into the index match large formula and every time I do I receive N/A. If anyone can help I would really appreciate it.