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.
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.
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
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.
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 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?
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!!!
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!!
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.
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?
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.
why cant i do for example >A1 as one of the criteria? It supports > if its for example ">0" but what if i want a dynamic cell reference for my > criteria?
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.
Quick question. What if I need the highest value based on a list that is changing, but its looking to a vlookup on another tab? For example, I have a list with all my items and lead times on one tab, and on the other tab I will create my orders addint the items. I need a cell to look at all the items on the order and return the highest lead time. Not sure how to make it happen.
Hi Leila. Thank you so much. How about if we have a table with minimum to maximum ranges, column 1 is minimum value, column2 is maximum value, the column 3 is a corresponding value for col1 to col2 range, and so on for row 2 onward. If say,I have a value within a min/max and I want to lookup the col3 value, what function can I use? Thank you.
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
I have very large data of Entries in parking GLs (8 GLs) in 150 branches and the O/S balance appears daily if its not NIL. i need to calculate start and end date of single balance subject to each branch and each GL and in graph form have to show which balance Br wise OR GLwise remained unsettled for how many days (start - End)
Can you make a video which will highlight max of dates but of a fix cell value. For e.g. I have an ID in a cell and has mutiple dates for it. Similarly i have multiple ids, so i want to highlight max date for ids.
Grab the file I used in the video from here 👉 pages.xelplus.com/max-min-file
whenever I search for some answer on TH-cam, 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.
Thanks for the AGGREGATE and MAXIFS and MINIFS fun , Teammate : )
Thanks Teammate :)
@@LeilaGharani cringe BOOOOOMERS XD XD XD jsjsjjsjsjsjsjsjsjsjsjjsjsjsjsjjsjsjsjsjsjjsjsjsjs ANNNA OOP
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.
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.
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.
Whenever I have problem at school or work..I know one of Leila's video can always help me out
😊
First half is great information second half brilliant turn around solution, you rock Leila as usual :)
Thank you for the kind feedback 😊
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 :)
Each time I watch one of your video, I learn something great. Thanks a lot Leila
That's good to hear :)
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.
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
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!
Thank you for your Videos. Few minutes watching, and I can save hours of work. Great job.
Thanks a lot! Very easy to understand. Love from Indonesia
Learnt it & show Magic to my boss at office.. Thanks cute leila 😍
Hi Leila, today this video proved to be of a great help. Nice idea Leila.
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!
really want to say thank you for these amazing tutorials
You're very welcome, Amy!
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.
Thanks, all you videos has helped me a lot.
Thanks for your training Learning new things every Thursday
Thanks for supporting my happy Excel Thursday :)
Glad i caught this video early. Good content, love the new intro
Me too :) Glad you like it.
Very useful and time saving formula. I am benefited. Thanks a lot.
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 :)
Great tips on the newer functions!
Glad you like it Doug.
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.
I use the Excel 2016 so thanks very much for providing the alternative solution.
Always saving me, love from Brazil!!!!
Great explanation of every single element in formula
Glad you like the tutorial.
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.
Mam you are always brilliant...
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?
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.
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 . 😀
Greatly I took advantage from this tutorial.. Thanks Dear
Great 👍
Its always great to start my day with your videos...Love them!
That's great to hear :)
Great detailed explanation Leila.
Glad you like the tutorial Enrique.
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!!!
Very useful!!! Thanks!
how can i use max and min function in live moving data?
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 :)
Thanks for providing practical education
You're very welcome.
Never used these before. Great video as always!
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.
A resourceful solution for my work! Thanks Leila!
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.
Thank you! This just made my spreadsheet working!
Glad it helped, Zoltan!
Great Leila Gharani. Many many Thanks From Bangladesh.
Glad you like it. Greetings to Bangladesh.
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.
as usual great video Leila, thanks. I'd like to apply this to summing with criterias, but do not how.
thank you so much! Aggregate is helpful for dates as well. Helped me to solve a long pending analysis!
THHHAAANNNNKKKKK YYYOOOOUUUU!!!!!! I actually understood it, thank you : )
My pleasure :)
Wow! Thanks ! Leila I was searching this type of formula...👍
Great! I'm glad I could help with that.
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 :)
Good solutions Leila, thanks.
My pleasure Luciano. Hope it's useful.
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.
Vielen Dank Liebe Leila 🤗
Danke dir liebe Katerina 😘
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?
As usual that was great!!!!!. Is there any other way than using aggregate ?
Really great video.
Appreciate your efforts.
Thanks a lot. Keep it up
It's my pleasure.
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 :)
why cant i do for example >A1 as one of the criteria? It supports > if its for example ">0" but what if i want a dynamic cell reference for my > criteria?
You are really very genius! Thank you for sharing this tutorial.
You're very welcome. Glad you like the video.
can you please suggest a way to get min/max value with row cell value in non adjacent cells and also the header value.
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.
Thanks.
Been trying to do this with vlookup and min max.
Now to find medianifs
Grate help to automate my schedule. Thank a ton
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
Great video, thanks!
Quick question. What if I need the highest value based on a list that is changing, but its looking to a vlookup on another tab? For example, I have a list with all my items and lead times on one tab, and on the other tab I will create my orders addint the items. I need a cell to look at all the items on the order and return the highest lead time. Not sure how to make it happen.
A lof of thanks for video lesson!
You're very welcome. I'm glad it's useful.
Hi leila, Thanks very useful vedio. Can we get results based on time like between i want max value criteria will time between 15:00 to 23:59
Hi Leila. Thank you so much. How about if we have a table with minimum to maximum ranges, column 1 is minimum value, column2 is maximum value, the column 3 is a corresponding value for col1 to col2 range, and so on for row 2 onward. If say,I have a value within a min/max and I want to lookup the col3 value, what function can I use? Thank you.
More great value from you, Leila! Thanks!
You are AMAZING!
Great Video... as always.. thank you Leila
My pleasure. I'm glad you like the video.
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
Thank you, I always stuck at min value with zero🙂. Now I know 👍♥️
Thanks. How can i get the 'region' value of the max value with maxifs
Excellent! Thanks Leila!
You're very welcome. Glad you like it.
Thank you very much for your informative video... keep your efforts...
My pleasure. I'm glad you find the video useful.
Amazing stuff. Thanks a lot for sharing :)
My pleasure, Bryan!
I have very large data of Entries in parking GLs (8 GLs) in 150 branches and the O/S balance appears daily if its not NIL. i need to calculate start and end date of single balance subject to each branch and each GL and in graph form have to show which balance Br wise OR GLwise remained unsettled for how many days (start - End)
your vids are vey usefull
I'm glad to hear that Edward.
Great content as always! Thank you.
Glad you like the tutorial Luciano!
Thank you Leila!
Great video!
Thank you Chris!
Hi,
Please provide an equation to display the "Country" having the max and min revenue values, keeping the other criteria same
Thanks for your video very much.
You're very welcome. Hope it's useful.
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?
How to capture a Max and Min value if the data is running live?
Thanks in advance
Mam, Thanks for your vaulable guidance !
My pleasure. I'm glad if the tutorials are helpful.
Can you make a video which will highlight max of dates but of a fix cell value. For e.g. I have an ID in a cell and has mutiple dates for it. Similarly i have multiple ids, so i want to highlight max date for ids.
Yep, she solved my problem (again).
Thank You VERY MUCH !
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 ??
Wonderful.👍
Glad you like it :)
What if the revenue have a 0 value, what should I add to the formula of min so I can find the next min value