Excel MAX or MIN with CONDITIONS (MAXIFS & AGGREGATE Method)
HTML-код
- Опубликовано: 15 июл 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Uncover the power of MINIFS and MAXIFS Excel functions. These tools are essential for extracting minimum or maximum values based on specific criteria. Suitable for Excel 2019 and Office 365 users, with alternatives for those on older versions.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/max-min-file
▪️ MAXIFS and MINIFS Functions:
Purpose: Easily calculate maximum and minimum values based on one or more criteria.
Example: Find the highest and lowest revenue for a specific year and division.
Procedure: Select your data range and criteria in the MAXIFS/MINIFS function.
▪️ Using AGGREGATE for Older Excel Versions:
Alternative: AGGREGATE function is a versatile substitute for those without access to the latest Excel versions.
Approach: Combine logical tests within the AGGREGATE function, using LARGE (for MAXIFS) and SMALL (for MINIFS) arguments.
Key Benefit: No need for Ctrl+Shift+Enter to execute array formulas, thanks to AGGREGATE's inherent handling of arrays.
▪️ Efficient Data Analysis:
Flexibility: Whether using the latest Excel functions or older versions, you can efficiently analyze data to extract meaningful insights.
Customization: Adapt formulas to fit various datasets and criteria, ensuring versatile application across different scenarios.
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.
More IFS functions - like SUMIFS: • How to Use SUMIFS, COU...
★ My Online Excel Courses ► www.xelplus.com/courses/
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
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!
#excel
Grab the file I used in the video from here 👉 pages.xelplus.com/max-min-file
whenever I search for some answer on RUclips, you were the only one who got it done! Kudos Ms. Leila!
Honestly, I've watched a few of your videos. It amazes me how easily you explain EXACTLY what I need after getting frustrated with Google searches and Reddit. I'm going to stop messing around and come to your channel in the first instance.
I've used AGGREGATE the way you show but I think I should get serious in getting an upgrade. Great presentation and as always easy to follow.
A great example of why we should all be thankful for the new functions -- and why users of older versions of Excel should consider upgrading!
True. There really have been great improvements in recent years.
Thanks for the AGGREGATE and MAXIFS and MINIFS fun , Teammate : )
Thanks Teammate :)
@@LeilaGharani cringe BOOOOOMERS XD XD XD jsjsjjsjsjsjsjsjsjsjsjjsjsjsjsjjsjsjsjsjsjjsjsjsjs ANNNA OOP
More great value from you, Leila! Thanks!
Never used these before. Great video as always!
this is awesome. thanks for this. i have been breaking my head for an 2 hours to solve a problem. Thanks to you Leila thats now solved in a minute.
Thank you for your Videos. Few minutes watching, and I can save hours of work. Great job.
A resourceful solution for my work! Thanks Leila!
Each time I watch one of your video, I learn something great. Thanks a lot Leila
That's good to hear :)
The divide by 0 trick is genius! Thanks for your videos, I continue to learn a lot from them.
My pleasure Benjamin. Glad you like my little trick :)
Your videos are so educational. They can be applied easily to real business situations. Thanks Leila.
I'm happy the content can be applied in real life. That's the goal.
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
Glad to help Sara!
I was really looking forward to see your new video, thank you, it is excellent as always. :) It helps me a lot in my work!
It's great you can apply it at work. Thanks for your support Gabriella.
thank you so much! Aggregate is helpful for dates as well. Helped me to solve a long pending analysis!
Glad i caught this video early. Good content, love the new intro
Me too :) Glad you like it.
Whenever I have problem at school or work..I know one of Leila's video can always help me out
😊
Hi Leila, today this video proved to be of a great help. Nice idea Leila.
Very useful!!! Thanks!
Great Video. I never used the Maxif function before. It was an eye-opening. Thank you Leila you hit it out of the park again. Thank you for the awesome work you put in these videos.
You're very welcome. Glad you found something new here.
Always saving me, love from Brazil!!!!
Thanks, all you videos has helped me a lot.
Thanks a lot! Very easy to understand. Love from Indonesia
Its always great to start my day with your videos...Love them!
That's great to hear :)
Thanks for your training Learning new things every Thursday
Thanks for supporting my happy Excel Thursday :)
First half is great information second half brilliant turn around solution, you rock Leila as usual :)
Thank you for the kind feedback 😊
Congrats on hitting 200000! You rock. One day Ill get there. Best inspiration.
Thank you very much! It's so great, never thought I'd ever reach that. You'll get there too!
Very useful and time saving formula. I am benefited. Thanks a lot.
Thanks a lot for another great video. We could also use 4 and 5 for Max and min respectively.
I use the Excel 2016 so thanks very much for providing the alternative solution.
Learnt it & show Magic to my boss at office.. Thanks cute leila 😍
Grate help to automate my schedule. Thank a ton
Never fail to be amazed by how much I learn in this channel even though I'm quite a seasoned macro user :)
It's good to hear you are able to learn some new things here :)
really want to say thank you for these amazing tutorials
You're very welcome, Amy!
Great video, thanks!
Muito bom, me ajudou muito. Obrigado por compartilhar! 🇧🇷
Very good, it helped me a lot. Thanks for sharing!
Mam you are always brilliant...
Thank you, I always stuck at min value with zero🙂. Now I know 👍♥️
Great and thanks .
You always in the front ... some kind of " smooth operator " .
I'm glad following your channel .
And I'm glad to have you here :)
Yep, she solved my problem (again).
Great tips on the newer functions!
Glad you like it Doug.
Thank you! This just made my spreadsheet working!
Glad it helped, Zoltan!
Thank You VERY MUCH !
Thanks!! This has really helped, and prevented me from transposing data!! 👍🏽
Is there a limit to the size of data that aggregate will work with?
Great content as always! Thank you.
Glad you like the tutorial Luciano!
You are really very genius! Thank you for sharing this tutorial.
You're very welcome. Glad you like the video.
Thanks for providing practical education
You're very welcome.
Great explanation of every single element in formula
Glad you like the tutorial.
Thank you Leila!
Thanks!
I would've loved to see this video 2 years ago, that would've helped me a lot with a sales report Excel sheet. I updated that Excel file 2 months ago, using the new Filter function to solve a similar situation than in the video.
Well, at least you now have it in your tool box :)
Greatly I took advantage from this tutorial.. Thanks Dear
Great 👍
You are AMAZING!
Hi Leila.. thanks for the refresher on these useful MAX and MIN techniques using multiple criteria. Love that divide by 1 trick to force the #DIV /0! errors inside the array in AGGREGATE using the SMALL function_num. Keep them coming. Thumbs up!!
Thanks for the thumbs up Wayne. Glad you like my little trick :)
Wow! Thanks ! Leila I was searching this type of formula...👍
Great! I'm glad I could help with that.
Really great video.
Appreciate your efforts.
Thanks a lot. Keep it up
It's my pleasure.
Good solutions Leila, thanks.
My pleasure Luciano. Hope it's useful.
A lof of thanks for video lesson!
You're very welcome. I'm glad it's useful.
Brilliant! As always, you are spot on and informative.
Looking forward to meeting you at the Excel Olympics 2019 Slovenia!
Glad you like the tutorial. It's great you'll be joining us in Slovenia!
@@LeilaGharani There's an Excel olympics? I can't work out if that's cool or not cool. Is it cool that I think that it is cool?
Also, is it pronounced MINIFS, or MIN-IF-S? I thought the "IFS" part of the word was just the plural of "IF", as in multiple IF statements?
Of course it's cool :) Where else can you see us nerds competing in an Excel decathlon 😁
For me IF-S is easier so that's what I go with.
Thank you very much for your informative video... keep your efforts...
My pleasure. I'm glad you find the video useful.
Excellent! Thanks Leila!
You're very welcome. Glad you like it.
Thanks for your video very much.
You're very welcome. Hope it's useful.
Great Video... as always.. thank you Leila
My pleasure. I'm glad you like the video.
Great Leila Gharani. Many many Thanks From Bangladesh.
Glad you like it. Greetings to Bangladesh.
Thanks Leila, can you do a video showing how to import data from word forms ? specially if you have more than one form in same word document
tnx ,,u r the best
Muito bom
Att, Gledson Programador Excel VBA
as usual great video Leila, thanks. I'd like to apply this to summing with criterias, but do not how.
Great detailed explanation Leila.
Glad you like the tutorial Enrique.
Mam, Thanks for your vaulable guidance !
My pleasure. I'm glad if the tutorials are helpful.
Amazing stuff. Thanks a lot for sharing :)
My pleasure, Bryan!
Thanks.
Been trying to do this with vlookup and min max.
Now to find medianifs
Hi Leila, Thanks for your lessons. Do you have way to find the next higher number of a fixed value in an array with 2 or more conditions?
Thanks a lot
so great, giọng hát quá hay
I am using Office 2019 so maxif and minif are available for me.Thanks a lot anyway..Please please please for future video idea checked products in different rows (with check box maybe)with quantity in next column copied in other sheet without empty rows ..very usefull for many people ..order form for example...By the way .Very nice dress in this video!!!
It's about the attitude
Someone just deliver information directly to the brains
Thank you your highness
Then let's just hope I don't mess up any brains :)
@@LeilaGharani
I don't think so
You're wonderful instructor
Thank you
THHHAAANNNNKKKKK YYYOOOOUUUU!!!!!! I actually understood it, thank you : )
My pleasure :)
It's great option for us... thanks
My pleasure. I'm glad the video is useful for you.
Great 👍👍👍👍👍
To be honest, I never used AGGREGATE function before as I think it was a little daunting at first. But as I slowly understand the use of it, I think I should apply it elsewhere. Anyhow I'm glad having E365 that I am able to use MAXIFS and MINIFS instead of writing complex functions already! Thanks for sharing. I'll definitely check that out in my next project.
True Alex. The IFS are much easier to use.
Vielen Dank Liebe Leila 🤗
Danke dir liebe Katerina 😘
thanks
nice lesson ..Leila please can you explain difference between LOOKUP & VLOOKUP
I like the use of the AGGREGATE function. Works like SUMPRODUCT. Usually do MAXIFS with array formulas. Though I use 2019, I need to share with other users who have earlier versions so MAXIFS is out of the question.
Very true. Thanks Michael.
As usual that was great!!!!!. Is there any other way than using aggregate ?
Great video!
Thank you Chris!
how extraordinary
Hello from New Zealand ...like the style of teaching and have already brought dashboard pack from you. ( haven’t seen all the videos yet )
That's great to hear! Wow, New Zealand ... are you braving the winter?
Leila Gharani yeah ...we are week away from going into spring ....but has been an ok winter . 😀
Thank you so much
You're most welcome.
your vids are vey usefull
I'm glad to hear that Edward.
Need a little bit more on *Aggregate Function*... Though understood it. Thanks! As always it was wonderful..👍👍
Took a while for me too. Glad you like the tutorial.
Very helpful thks
My pleasure. Glad the video is useful.
Wonderful.👍
Glad you like it :)
thanks...
Thank you
You're very welcome. Always nice to see your comments.
What can I say.... brilliant as always.
This was very helpful. I want to use the same approach to compute the average of the data set whilst ignoring zeros. Kindly assist.
Very Nice video.
Glad you like it :)