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

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/max-min-file

[@MangJuArnz]

whenever I search for some answer on KZbin, you were the only one who got it done! Kudos Ms. Leila!

[@brianbean4882]

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.

[@JimFikes]

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!

[@LeilaGharani]

True. There really have been great improvements in recent years.

[@carpevinum9537]

Whenever I have problem at school or work..I know one of Leila's video can always help me out

[@LeilaGharani]

😊

[@bn_ln]

The divide by 0 trick is genius! Thanks for your videos, I continue to learn a lot from them.

[@LeilaGharani]

My pleasure Benjamin. Glad you like my little trick :)

[@abdallah.kandiel]

It's about the attitude Someone just deliver information directly to the brains Thank you your highness

[@LeilaGharani]

Then let's just hope I don't mess up any brains :)

[@abdallah.kandiel]

@@LeilaGharani I don't think so You're wonderful instructor Thank you

[@VenkiDas]

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.

[@ronvds4713]

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.

[@Oprimaita]

Each time I watch one of your video, I learn something great. Thanks a lot Leila

[@LeilaGharani]

That's good to hear :)

[@kamelgameel1250]

First half is great information second half brilliant turn around solution, you rock Leila as usual :)

[@LeilaGharani]

Thank you for the kind feedback 😊

[@sundaramnarayanan6776]

Thanks for your training Learning new things every Thursday

[@LeilaGharani]

Thanks for supporting my happy Excel Thursday :)

[@excelisfun]

Thanks for the AGGREGATE and MAXIFS and MINIFS fun , Teammate : )

[@LeilaGharani]

Thanks Teammate :)

[@nonbinarybastard]

@@LeilaGharani cringe BOOOOOMERS XD XD XD jsjsjjsjsjsjsjsjsjsjsjjsjsjsjsjjsjsjsjsjsjjsjsjsjs ANNNA OOP

[@mostafa4321]

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.

[@LeilaGharani]

You're very welcome. Glad you found something new here.

[@alexkim7270]

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.

[@LeilaGharani]

True Alex. The IFS are much easier to use.

[@saracrosby5237]

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

[@LeilaGharani]

Glad to help Sara!

[@msoc69]

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.

[@LeilaGharani]

Very true. Thanks Michael.

[@anillpatel]

Your videos are so educational. They can be applied easily to real business situations. Thanks Leila.

[@LeilaGharani]

I'm happy the content can be applied in real life. That's the goal.

[@super_cute_jung]

really want to say thank you for these amazing tutorials

[@LeilaGharani]

You're very welcome, Amy!

[@fajrieyulio6492]

Thanks a lot! Very easy to understand. Love from Indonesia

[@IvanBeles]

Thank you for your Videos. Few minutes watching, and I can save hours of work. Great job.

[@vida1719]

Great explanation of every single element in formula

[@LeilaGharani]

Glad you like the tutorial.

[@jayaxe79]

Never fail to be amazed by how much I learn in this channel even though I'm quite a seasoned macro user :)

[@LeilaGharani]

It's good to hear you are able to learn some new things here :)

[@yashsomaiya8141]

Hi Leila, today this video proved to be of a great help. Nice idea Leila.

[@ExcelWithChris]

Congrats on hitting 200000! You rock. One day Ill get there. Best inspiration.

[@LeilaGharani]

Thank you very much! It's so great, never thought I'd ever reach that. You'll get there too!

[@salehmohammad5170]

Learnt it & show Magic to my boss at office.. Thanks cute leila 😍

[@euniceesinam2573]

I use the Excel 2016 so thanks very much for providing the alternative solution.

[@shayanalamian4743]

Thanks for providing practical education

[@LeilaGharani]

You're very welcome.

[@sazzadhossain4136]

Very useful and time saving formula. I am benefited. Thanks a lot.

[@salvadoriniguez1156]

Thanks, all you videos has helped me a lot.

[@Uriel_Soberanes]

Very useful!!! Thanks!

[@davidsouza2844]

Always saving me, love from Brazil!!!!

[@leoncornelius5062]

Thanks!! This has really helped, and prevented me from transposing data!! 👍🏽

[@leoncornelius5062]

Is there a limit to the size of data that aggregate will work with?

[@pemayeshi4694]

Appreciate your video. I want to know why we could not find the Maxifs and Minifs values of positive and negative numbers together in one column.

[@lephterisp]

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!!!

[@sarfarajkureshi6569]

Mam you are always brilliant...

[@aftabhameedkhan9213]

Brilliant! As always, you are spot on and informative. Looking forward to meeting you at the Excel Olympics 2019 Slovenia!

[@LeilaGharani]

Glad you like the tutorial. It's great you'll be joining us in Slovenia!

[@ricos1497]

@@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?

[@LeilaGharani]

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.

[@salehmohammad5170]

Greatly I took advantage from this tutorial.. Thanks Dear

[@LeilaGharani]

Great 👍

[@DougHExcel]

Great tips on the newer functions!

[@LeilaGharani]

Glad you like it Doug.

[@lencsezoltan]

Thank you! This just made my spreadsheet working!

[@LeilaGharani]

Glad it helped, Zoltan!

[@wayneedmondson1065]

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!!

[@LeilaGharani]

Thanks for the thumbs up Wayne. Glad you like my little trick :)

[@harshababu8321]

As usual that was great!!!!!. Is there any other way than using aggregate ?

[@SholaDsdg]

A resourceful solution for my work! Thanks Leila!

[@enriquedominguez9709]

Great detailed explanation Leila.

[@LeilaGharani]

Glad you like the tutorial Enrique.

[@vijaygusain119]

Its always great to start my day with your videos...Love them!

[@LeilaGharani]

That's great to hear :)

[@ravimistry777]

thank you so much! Aggregate is helpful for dates as well. Helped me to solve a long pending analysis!

[@appliedelearning8651]

Great Leila Gharani. Many many Thanks From Bangladesh.

[@LeilaGharani]

Glad you like it. Greetings to Bangladesh.

[@ckokse]

Never used these before. Great video as always!

[@NeerajPareek1975]

Need a little bit more on *Aggregate Function*... Though understood it. Thanks! As always it was wonderful..👍👍

[@LeilaGharani]

Took a while for me too. Glad you like the tutorial.

[@moisestatis5418]

as usual great video Leila, thanks. I'd like to apply this to summing with criterias, but do not how.

[@Luciano_mp]

Good solutions Leila, thanks.

[@LeilaGharani]

My pleasure Luciano. Hope it's useful.

[@rajal8787]

Thank you so much, Leila! I was so excited to try this because it but it gives me an error. I believe the error stems from criteria name selection. I simply typed the criteria names (i.e., Price, Year), on side of dataset, selected the data range and applied the formula. Did I miss something?

[@SamMolinero]

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.

[@LeilaGharani]

Well, at least you now have it in your tool box :)

[@qualitysnaps680]

Hello from New Zealand ...like the style of teaching and have already brought dashboard pack from you. ( haven’t seen all the videos yet )

[@LeilaGharani]

That's great to hear! Wow, New Zealand ... are you braving the winter?

[@qualitysnaps680]

Leila Gharani yeah ...we are week away from going into spring ....but has been an ok winter . 😀

[@nelidabarachina9369]

I am so grateful of your tutorials. I tried using this formula and now I’m having issues My data sets have 0 values. It is not calculating properly.

[@roselsimpson858]

Glad i caught this video early. Good content, love the new intro

[@LeilaGharani]

Me too :) Glad you like it.

[@pavel2k]

Great video, thanks!

[@Otisawide]

Thank you Leila!

[@kshitijshinde8592]

Grate help to automate my schedule. Thank a ton

[@katerina6495]

Vielen Dank Liebe Leila 🤗

[@LeilaGharani]

Danke dir liebe Katerina 😘

[@lillkidsgame3842]

THHHAAANNNNKKKKK YYYOOOOUUUU!!!!!! I actually understood it, thank you : )

[@LeilaGharani]

My pleasure :)

[@mosesmukambojr8707]

Thanks. Been trying to do this with vlookup and min max. Now to find medianifs

[@gabriellaprievara7379]

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!

[@LeilaGharani]

It's great you can apply it at work. Thanks for your support Gabriella.

[@aizidoros]

You are AMAZING!

[@alihasan7067]

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

[@ajexcel]

Wow! Thanks ! Leila I was searching this type of formula...👍

[@LeilaGharani]

Great! I'm glad I could help with that.

[@thevloglife105]

You are really very genius! Thank you for sharing this tutorial.

[@LeilaGharani]

You're very welcome. Glad you like the video.

[@tha2irtalib343]

Great and thanks . You always in the front ... some kind of " smooth operator " . I'm glad following your channel .

[@LeilaGharani]

And I'm glad to have you here :)

[@shipingli7590]

Thanks for your video very much.

[@LeilaGharani]

You're very welcome. Hope it's useful.

[@chrisbishop5568]

Thank you for all your helpful Videos, you've helped me a ton I do have one question. I cannot find this anywhere and it seems that it would be easy. How can i find the middle value? say i have 20 Games I can get the Highest price and lowest price game but how can i get the middle priced one? everything i see online shows me how to average the numbers but i just want the formula to grab the middle price so i have a list of High, Medium, and low price. Thank you

[@yurd563]

A lof of thanks for video lesson!

[@LeilaGharani]

You're very welcome. I'm glad it's useful.

[@Prachigavali10]

Really great video. Appreciate your efforts. Thanks a lot. Keep it up

[@LeilaGharani]

It's my pleasure.

[@yulinliu850]

Excellent! Thanks Leila!

[@LeilaGharani]

You're very welcome. Glad you like it.

[@dunnobe]

Yep, she solved my problem (again).

[@wojwojT]

Thank You VERY MUCH !

[@marclim6968]

can you do a video to further discuss the different functions of the AGGREGATE function i.e. count or counta. I am trying to do a formula using the COUNTIF/S function to count specific values i.e. answers in a survey with answers ranging from 1 to 5. i wish to count how many people answered 1, 2, 3 and so on but excluding any hidden rows. and since the AGGREGATE function is the only one that seem to have a function for ignoring hidden rows, i am trying to combine both to make it a bit more dynamic? hope you could help me with this. Thank you.

[@wofdigy]

Great Video... as always.. thank you Leila

[@LeilaGharani]

My pleasure. I'm glad you like the video.

[@09shirish]

Mam, Thanks for your vaulable guidance !

[@LeilaGharani]

My pleasure. I'm glad if the tutorials are helpful.

[@towfeek1338]

Thank you very much for your informative video... keep your efforts...

[@LeilaGharani]

My pleasure. I'm glad you find the video useful.

[@eprzepiora]

your vids are vey usefull

[@LeilaGharani]

I'm glad to hear that Edward.

[@ThuThu-br6hf]

so great, giọng hát quá hay

[@juniorsalman]

Thank you, I always stuck at min value with zero🙂. Now I know 👍♥️

[@RaviTeja-mp1fh]

This is great, i apply your content daily. But if i want max 10 or min 10 instead of single value.

[@SolidSnake59]

You have to use array formula, LARGE function (14 in AGGREGATE) and also use ROW function for [k] argument. First you need to select range of 10 cells, type below function (adjust ranges ofc) and press Ctrl+Shift+Enter. Eg. =AGGREGATE(14,4,($A$3:$A$15=$E$2)*($B$3:$B$15=$F$2)*$C$3:$C$15,ROW()-ROW($A$1))

[@keishrich]

While I like the ease of the first solution, I think there is more in depth learning using AGGREGATE. thanks Leila!

[@chistenator]

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?

[@chrism9037]

Great video!

[@LeilaGharani]

Thank you Chris!

[@simfinso858]

Very Nice video.

[@LeilaGharani]

Glad you like it :)

[@banwarifageriaexcelclasses1447]

nice lesson ..Leila please can you explain difference between LOOKUP & VLOOKUP

[@bryannntan]

Amazing stuff. Thanks a lot for sharing :)

[@LeilaGharani]

My pleasure, Bryan!

[@zaighamuddinfarooqui1705]

Wonderful.👍

[@LeilaGharani]

Glad you like it :)

[@ScottAnders62]

More great value from you, Leila! Thanks!

[@brahmmamb24]

It's great option for us... thanks

[@LeilaGharani]

My pleasure. I'm glad the video is useful for you.

[@lpanebr]

Great content as always! Thank you.

[@LeilaGharani]

Glad you like the tutorial Luciano!

[@jonalazarre6200]

Thank You so much your videos have been very helpful! I am the cofounder of a small nonprofit organization, we are using Google Form and Google Sheets for our database to document our actions. I do have a question, if I'm trying to Maxifs to figure out the latest did something was done; however I am using data that are responses from a Google Form. So some may have multiple answers in the same cell that divided by a coma. I am looking for a way to record the lastest date for a specific action among multiple answers. For example on 01/03/23 the following types of donations were made during one visit (school kit, clothes kit, food kit), if I would like to get the latest date a food kit was given, how would I go about it? Thanks for your help.

[@rishabhmehta3364]

Thanks a lot for another great video. We could also use 4 and 5 for Max and min respectively.

[@NikunjDM]

धन्यवाद, लीला

[@LeilaGharani]

You're very welcome :)

[@carltagbor]

This was very helpful. I want to use the same approach to compute the average of the data set whilst ignoring zeros. Kindly assist.

[@michaelkan01]

Very helpful thks

[@LeilaGharani]

My pleasure. Glad the video is useful.

[@maheshcherukuri8404]

Hi, Please provide an equation to display the "Country" having the max and min revenue values, keeping the other criteria same

[@مقدادحديد]

Beautiful face and valuable information Thanks to the Queen of Excel

[@LeilaGharani]

Glad you like the video 😊

[@enzofrancescoli1260]

@@LeilaGharani You're not glad that he thinks you're beautiful?

[@ronvds4713]

Grow up