Excel MIN and MAX with Criteria
November 3, 2020
Thank you for reading the news, and you'll get the next email in two weeks, on November 17th.
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
What's the lowest quantity in an order for pens? In Excel 2016 and earlier, you had to build your own MIN IF and MAX IF formulas, to find minimum and maximum values, based on criteria. For example, this formula finds the lowest quantity for the product name in cell B4 (Ctrl+Shift+Enter)
It's easier now, if you have Excel 2019, or Excel for Office 365, because you can use the new MINIFS and MAXIFS functions. For example, this formula finds the minimum quantity for the customer selected in cell C3, and the product name in cell B6.
Note: There's a spill formula in cell B6 to create a unique list of products. The MINIFS formula refers to that cell with the spill operator -- B6# -- so the MINIFS results spill down too.
See more examples, and download the sample file, on the MIN and MAX Functions page.
If you spend lots of time applying filters and clearing them, here's a way to save time - put the Clear All button on your Quick Access Toolbar (QAT).
Then, just click it, to clear all the filters in an Excel table, or pivot table.
Another time saver -- select cells in 2 or more table columns, and a macro clears the filters on just those columns. The macro is on my AutoFilter Macros page.
Tip: Put that macro in your Personal Workbook, then add it to the QAT
Here are a couple of Excel-related articles that you might find useful or interesting.
Dashboards: Mynda Treacy has a new Power BI dashboard tutorial. There's a step-by-step video, and you can download the sample workbook, to follow along. Mynda offers full courses too, and they're 20% off, from Nov 3rd to 12th. Check out Mynda's Excel dashboard courses, and her Power BI courses.
Power Query: Erik Svensen shares his tips to create fewer steps in Power Query, and make them easier to read and understand. (Level - Int/Adv)
Sunday morning, I looked out the back window, and could tell right away that it was November. All the leaves had fallen off our catalpa tree overnight. It's a chore to rake them up, but as we say here in Canada, it's better than shovelling snow! (which will happen all too soon)
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2020/20201103ctx.html
I'll also post any article updates or corrections there.
That's it for this week! If you have any comments or questions, send me an email.
Last updated: November 2, 2020 2:51 PM