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:
- Nên hay không mua bán nhà đất Long Biên ở thời điểm hiện tại?
- Gem Sky World Long Thành rộng bao nhiêu? Xem ngay để biết
- Vị trí khu đô thị Lakeview City có gì đắt giá – xem ngay!
- Giải mã lý do mua bán chung cư MeeyLand ngày càng sôi động
- Tuyệt chiêu hack like Zalo trên điện thoại kéo tương tác siêu hiệu quả
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 ??
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
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!
I need to get the lastest date from a list, how do i do that
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.
Thank You VERY MUCH !
Grate help to automate my schedule. Thank a ton
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.
I want your help to sort my problem in excel
Mine don't have maxif or minif, its a biggie, sure wish I had it. Lol
Really great video.
Appreciate your efforts.
Thanks a lot. Keep it up
I think AGGREGATE function should include "ignore zero values" options. it could be available in the future.
Hi Leila, today this video proved to be of a great help. Nice idea Leila.
Yep, she solved my problem (again).
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!
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 💖🧡💛💚💙💙💜🙄😤😠🥳😇🤭🤪🤯😬🙃🥺🥺🥺🥺🥺🥺🥺🥺🥺🥺🥺🥺✨✨✨✨✨✨✨✨
UGH u sonud like me MOM 💫🤣🤩🦄🦄👀✌💅🌟🌟🌟🌟🌟🌟🌟🌟🌟🌟🌟🌟🌟🌟🌚💖🧡💛💚💙💜
i cant find MAXIFS ?
Thank you, I always stuck at min value with zero🙂. Now I know 👍♥️
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.
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
We can also use DMAX & DMIN in older versions of Excel for same.