Instead of writing complicated MIN IF or MAX IF formulas in Excel, you can quickly get those numbers in a pivot table. See the easy steps in the video below.
Video: Show MIN IF or MAX IF in Pivot Table
When you’re working with Excel data, a pivot table makes it easy to find the lowest and highest values for a specific item, or combination of items.
The pivot table automatically creates a unique list of items, and you can set the amounts to show as Min or Max, instead of Sum or Count.
This 3-minute video shows the steps, and there are written steps on the MIN and MAX Function Examples page on my Contextures site.
MINIFS and MAXIFS Formulas
If you’re using Excel 365, or Excel 2019, you can use the MINIFS and MAXIFS functions to calculate the minimum or maximum, based on one or more criteria.
For example, in the screen shot below, the formulas show min and max results for each product, for the selected customer.
Here’s the MINIFS formula in cell C6, to calculate the lowest quantity for File Folders, for customer ABC:
- =MINIFS(tblProdCust[Qty], tblProdCust[Product], B6, tblProdCust[Cust], $C$3)
Pivot Table MIN IF and MAX IF
If you don’t have those MINIFS and MAXIFS functions, or if you’d like to avoid complicated formulas, use a pivot table to quickly show the minimum and maximum values.
For example, in the screen shot below, I built a pivot table with:
- Product field in the Rows area
- TotalPrice field in the Values area (twice)
Instead of showing the TotalPrice values as SUM, I used the “Summarize Values By” settings to change the functions:
- In column B, I changed the summary function to Min
- In column C I changed the summary function to Max
Now, without any formulas, I can see that:
- the minimum for Bran (48.62)
- the maximum for Pretzels (97.65)
Tip: To refer to those Min/Max values in worksheet formulas, use the GetPivotData Function to pull the numbers you need.
Get the Sample File
To get the MIN IF or MAXIF With Excel Pivot Table sample file, and more formula examples, go to the Excel MIN and MAX functions page on my Contextures site.
The zipped file is in Excel xlsx format, and does not contain macros.
That page also has a video that shows how to pull values from a pivot table, using the GetPivotData Function.
_____________________
Show MIN IF or MAXIF With Excel Pivot Table
_____________________