Find MIN IF and MAX IF From Excel Pivot Table

In Excel, you can use array formulas to find MIN IF and MAX IF. For example:

• What was the lowest total order price for the Carrot Bars product?
• What was the highest price for that product?

In the screen shot below, you can see the formula that calculates the MIN IF price:

=MIN(IF(Sales_Data[Product] =”Carrot”, Sales_Data[TotalPrice]))

Use a Pivot Table Instead

An easier way to find the lowest and highest values for a specific item, is to use a pivot table. It automatically creates a list of all the products, and you can select which function or functions you want to see in the totals.

In the screen shot below, the TotalPrice field has been added twice to the values area. In one field, the summary function has been changed to MIN and in the other field it was changed to MAX.

This is much quicker and easier than manually create a product list and entering the array formulas.

You can read more about the pivot table summary functions on my Contextures website.

Video: Find MIN IF and MAX IF with a Pivot Table

In this video, you’ll see how to create a pivot table and show the list of products. Then, add the Total Price field, and see the highest and lowest order totals for each product.

____________________