Excel Magic Trick 1285: OR Criteria & AND Criteria for Max & Min Calculations (5 Examples)

File: excelisfun.net...
Download File: excelisfun.net/
Learn how to calculate the MIN and MAX values when there is complex criteria that mixes OR Logical Tests and AND Logical Tests (OR Criteria and AND Criteria). See 5 examples:
(00:11) Introduction
1. (01:10) DMIN and DMAX functions
2. (03:33) MIN, MAX, IF, MATCH, ISNUMBER functions and array operations.
(09:02) Clipboard Trick to load up clipboard with formula elements
3. (09:37) AGGREGATE, MATCH, ISNUMBER functions and array operations.
(12:09) See that MINIFS & MAXIFS continue the long tradition of not allowing the criteria_range argument to perform array operations, just like SUMIF, SUMIFS, COUNTIFS and the other “IFS” functions.
4. (13:59) MINIFS, MAXIFS, MIN, MAX and Function Argument Array Operations to enact OR Criteria in the MINIFS and MAXIFS functions!.
5. (16:59) AGGREGATE, MINIFS, MAXIFS, and Function Argument Array Operations.
(18:34) Summary
Purchase "Office 2016 for Office 365" products.offic...
Go to Office Insider site to download "Office Insider Version" products.offic...
Office Insider Version: 16.0.6568.1011 or 16.0.6568.2025

[@excelisfun]

+Michael Gowlett I am glad that the videos are useful for you! Thanks for the kind words!

[@SndfOmar]

Million thanks to you. You are THE greatest Excel Teacher alive.

[@excelisfun]

Thanks for your kind words. It is easy when excel is so much fun : )

[@michaelgowlett5036]

I've only recently subscribed to your videos but I must admit the way you explain them its really effective, thanks for doing these videos keep up the good work. I especially like the way you almost go from the basic elements of a function first in your videos then expand upon them during the length of the video.

[@rajagopalang1990]

Super Cool. Mike as always super video's on the MIN and the MAX values. Grateful.

[@excelisfun]

+Rajagopalan G Glad you like them!

[@wmoore2011]

thanks a million this is a life saver just started a new job have not touched EXCEL for a while whew!! these are great video's whatever you need to learn is here!!

[@excelisfun]

+Wesley Moore You are welcome a million!!

[@lichkaven8635]

我很喜欢你的视频。谢谢。
I love your videos very much .thank you

[@excelisfun]

+Lich Kaven You are welcome!

[@guitarfan1989]

Thank you for making these videos, very interesting!

[@excelisfun]

+guitarfan1989 You are welcome!

[@MySpreadsheetLab]

wow! Aggregate rules! and control cc is rad!

[@excelisfun]

+Kevin Lehrbass AGGREGATE does seem to have a lot of uses....

[@krn14242]

Thanks Mike, you nailed MIN and MAX down I think.

[@excelisfun]

+krn14242 Tou are welcome, WRH!!!

[@TOUFIKELASATEY]

Thank you very much for all your efforts. Excel is very easy whith you.

[@excelisfun]

+TOUFIK EL ASATEY You are welcome!

[@joramirez159357]

Thank you man, so useful!

[@forzaalgiers2045]

Great effort, great job. Thank you.

[@excelisfun]

+Samir ALGIERS You are welcome!

[@MarkCBB86]

Awesome Video, Thank you

[@excelisfun]

+Mark Blackburn You are welcome!

[@anaszaeem4532]

Thank you
it's cool formulas.

[@excelisfun]

+Anas Zaem Glad you like it!

[@flydipdrop]

Hi Instead of Min (in the second example) using this method could you SUM the SMALLest 3 numbers? I've tried using the aggregate function but doesnt seem to work? Using your previous example in the k part of a SMALL function {1,2,3}, i cannot then continue with the aggregate as the bracket deletes the curly bracket...any ideas?

[@byronburns4400]

Hello, I am trying to get the min value if the value is above 50% of the average. Is there a way to use a formula as a criteria? I have tried ">$A$1" as well as >$A$1. Thanks for your help.