# 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))

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.

### 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.

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

### 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)