Contextures

Contextures News 2020-11-03

Excel MIN and MAX with Criteria

November 3, 2020

MIN and MAX with criteria, filter time saver, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

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.

MIN and MAX Criteria

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)

  • =MIN(IF($E$4:$E$11=B4,$F$4:$F$11))

MIN IF formula

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.

  • =MINIFS(tblProdCust[Qty], tblProdCust[Product], B6#, tblProdCust[Cust], $C$3)

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.

MINIFS formula

Filter Time Saver

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.

clear all button on QAT

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

clear specific columns with macro

Excel Articles

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)

Also see: My Excel Products || Previous Issues

November

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)

weekly photo

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.

Debra Dalgleish
dsd@ contextures.com

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

contextures newsletter info

 

Last updated: November 2, 2020 2:51 PM