Excel MAX or MIN with CONDITIONS (MAXIFS & AGGREGATE Method)

admin July 1, 2020

Is there an Excel Max IF or MIN IF function? If you have Excel for Office 365 or Excel 2019, you can use MAXIFS and MINIFS functions which allow us to find the maximum or minimum value in a data set based on multiple conditions. For example if you wanted to find the MIN value but exclude zero values, you can use the MINIFS function to do that.

If you have an older version of Excel, you can use an alternate method. The common solution is {=Max(If (array) )} but you need to press Control Shift Enter (CSE).
Another method to get the maximum or minimum value based on criteria is to use the AGGREGATE function. The advantage of AGGREGATE is you don’t need to press control shift enter. Just enter. You need a little trick to get to work. We cover that in this video.

⯆ DOWNLOAD the workbook here:

More IFS functions – like SUMIFS:

★ My Online Excel Courses ►

✉ Subscribe & get my TOP 10 Excel formulas e-book for free

EXCEL RESOURCES I Recommend:

Get Office 365:
Microsoft Surface:

GEAR
Camera:
Screen recorder:
Microphone:
Lights:

More resources on my Amazon page:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#MsExcel

Nguồn: https://fanstalkwrestling.com

Xem thêm bài viết khác: https://fanstalkwrestling.com/tong-hop/

Xem thêm Bài Viết:

22 Comments

Reply joujou milor • 3 months ago

Hi Leila thanks a lot for this video, besides this I realy wonder how can I get the row number of the result returned by max if ??
Reply Sara Crosby • 3 months ago

Thank you for making these videos, you've explained this in a very easy way to understand. I've been trying to figure this out for hours until I found your video
Reply Edser Thomas • 3 months ago

I have a row of data that alternates between hourly rate and an effective date. I thought i could use this function =MAXIFS(F27:N27,F27:N27,"<E9") E9 is the date of the time sheet. I thought it would find the highest number that is lower than the date of the time sheet. it returns zero. The idea is to maintain previous records based on the rates applicable at the time. Please help me Obi-Liela. Your my only hope!
Reply Amit Shikhare • 3 months ago

I need to get the lastest date from a list, how do i do that
Reply Jasvinder Jassel • 3 months ago

Can someone help me, how will you workout the smallest number with AGGREGATE Function only, requiring 2 conditions to be met in one column e.g. Utility and Game. Thanks in advance. Managed to get this done but have to use Curly Brackets{} for criteria or conditions.=AGGREGATE(14,3,1*($B$4:$B$33={"Utility","Game"})*$D$4:$D$33,1), If someone knows other way, please let me know.
Reply Wojciech Trzepizur • 3 months ago

Thank You VERY MUCH !
Reply Kshitij Shinde • 3 months ago

Grate help to automate my schedule. Thank a ton
Reply Syed Al-e-Raza • 3 months ago

Hi. According to your sheet, if I need to know the minimum value of the current year only and in the sheet suppose, current year has some zero values in it, how we can use the aggregate function. I tried it but the result has always zero. Thanks for your valuable time.
Reply Jaswant Singh Chauhan, Life insurance • 3 months ago

I want your help to sort my problem in excel
Reply william johnston • 3 months ago

Mine don't have maxif or minif, its a biggie, sure wish I had it. Lol
Reply prashant gavali • 3 months ago

Really great video.
Appreciate your efforts.
Thanks a lot. Keep it up
Reply Serkan Kaya • 3 months ago

I think AGGREGATE function should include "ignore zero values" options. it could be available in the future.
Reply YASH SOMAIYA • 3 months ago

Hi Leila, today this video proved to be of a great help. Nice idea Leila.
Reply dunnobe • 3 months ago

Yep, she solved my problem (again).
Reply Kayra • 3 months ago

The videos are great! Is there a way to connect with you offline? I am among the few females looking to increase tech (Excel, Tableau, Python) E-learning in India. Your help would be really appreciated!
Reply Bryce Webster • 3 months ago

SISTER who would have an old ver JUST UPDATE BOOOMERS its not hard U JUS T TO LAZY TO LEARN dat y u watch this vid >:P 💖🧡💛💚💙💙💜🙄😤😠🥳😇🤭🤪🤯😬🙃🥺🥺🥺🥺🥺🥺🥺🥺🥺🥺🥺🥺✨✨✨✨✨✨✨✨
Reply Bryce Webster • 3 months ago

UGH u sonud like me MOM 💫🤣🤩🦄🦄👀✌💅🌟🌟🌟🌟🌟🌟🌟🌟🌟🌟🌟🌟🌟🌟🌚💖🧡💛💚💙💜
Reply Obaid Alkaabi • 3 months ago

i cant find MAXIFS ?
Reply juniorsalman • 3 months ago

Thank you, I always stuck at min value with zero🙂. Now I know 👍♥️
Reply The Remnant of Joseph • 3 months ago

Excellent! A lot of power with AGGREGATE. I recommend aggregate to get the nth largest (or smallest) value and to expand knowledge base of Excel. Well done, Leila.
Reply Ty Budd • 3 months ago

Cant get this scenario, been looking at all these videos, maybe you can help me:

Explanation

If any cell in Column A=T1

and

If Column C Range <= then today's current date

and

Column F has the lowest Number matching the above 2 criteria

Show the value on that row in from Column D
Reply Aleem Habib • 3 months ago

We can also use DMAX & DMIN in older versions of Excel for same.