Today’s Excel tip is a question I was asked recently by a subscriber. It was to achieve an easy way to find the lowest and highest selling prices for a range of products. So, Welcome back to another blog post in my Excel tips #FormulaFriday 2019 series. The data set contained sales records for Sales People along with selling prices of goods. The minimum and the maximum price that the Salesperson agreed with the customer per product was needed. This is an easy job for the MINIF and the MAXIF Excel functions.

Find The Lowest And Highest Selling Prices.

So, let’s start with an extract of the type of data set that was used. This is significantly smaller than the original data set which held around 5000 plus records every month. But, the basis of the calculation is still the same.

Using Excel MINIFS Function

First, we can look for the smallest, lowest of the minimum selling price that a product sold for. In summary, the MINIFS function returns the smallest numeric value that meets one or more criteria in a range of values. It can be used with criteria which can be dates, numbers, and even logical operators such as >,<,= and also wildcards. The syntax is

 =MINIFS (min_range, range1, criteria1, [range2], [criteria2], …)

where the arguments are as follows-

• min_range – Range of values used to determine the minimum.
• range1 – The first range to evaluate.
• criteria1 – The criteria to use on range1.
• range2 – [optional] The second range to evaluate.
• criteria2 – [optional] The criteria to use on range2.

So, we can apply this to our sample data set with the formula as below.  The minimum range is E2 to E13.  The first (or only range in this example).

In this first example, I have only used the one criteria which is the Product Code.  Excel has returned the Minimum Selling Price in our data range which is 13.98.  Let’s add in another level, and find the Minimum Selling Price for each of  Sales Person or Sales ID.  Here all we do is add in another criteria and criteria range.  So let’s take a look at the minimum selling price for FIRESAX for Sales Person SUSANB.

SUSANB has several sales prices for FIRESAX but the lowest or minimum is 3.99 in the data set. Yes, it is as easy as that to solve this problem. We know the Minimum price but what about finding the Maximum price. Well, yes you guessed it, we just need to use the MAXIFS function.

Using Excel MAXIFS Function.

The MAXIFS function returns the largest numeric value that meets one or more criteria in a range of values. It can be used with criteria which can be dates, numbers, and even logical operators such as >,<,= and also wildcards. The syntax is

 =MAXIFS (max_range, range1, criteria1, [range2], [criteria2], …)

where the arguments are as follows-

• max_range – Range of values used to determine the minimum.
• range1 – The first range to evaluate.
• criteria1 – The criteria to use on range1.
• range2 – [optional] The second range to evaluate.
• criteria2 – [optional] The criteria to use on range2.

So, again we can apply this to our sample data set with the formula as below.  The maximum range is E2 to E13.  The first (or only range in this range).

In this first example, I have only used the one criteria which is the Product Code.  Excel has returned the Maximum Selling Price in our data range which is 15.99.  Let’s add in another level, will we?.  We will find the maximum Selling Price for each Sales Person or Sales ID.  Here all we do is add in another criteria and criteria range.  So let’s take a look at the maximum selling price for FIRESAX for Sales Person SUSANB.

So, that is how to to use the MINIFS ad MAXIFS functions in Excel.

What Next? Want More Tips?

If you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips and check out all of my Formula Friday Blog posts below.

How To Excel At Excel – Formula Friday Blog Posts.

Find Missing Values In Excel. Formula Friday(Opens in a new browser tab)

Do You Need Help With An Excel Problem?.

Finally, I am pleased to announce I have teamed up with Excel Rescue,  where you can get help with Excel FAST. Why not check it out?.