Get Latest Price for Specific Product

In a previous article, we combined the MAX and IF functions, to find the highest price for a specific product.

minmax08

Today we’ll use that technique to find the latest date that a product’s price was changed. Then, using the product name and that date, we can find the latest price for the product – even if it isn’t the highest price.

Product Pricing Table

In this example, there is a price list in cells A1:C9. There are three columns: Product, Date and Price.

maxiflatestprice01

Find the Latest Date

If we want to find the latest price for Pens, the first step is to figure out the last date that its price was changed. To do that, we’ll use the MAX and IF functions. This is an array function, so you’ll press Ctrl+Shift+Enter, instead of just pressing Enter, after typing the formula.

  • Enter the product name -- Pens -- in cell A12.
  • To calculate the latest date in cell B12, enter this formula, and press Ctrl + Shift + Enter:

    =MAX(IF($A$2:$A$9=A12,$B$2:$B$9))

maxiflatestprice02

The formula looks for the selected product name in column A -- $A$2:$A$9=A12

Then, it finds the highest date for that product in column B -- $B$2:$B$9

Calculate the Latest Price

Next, we’ll use a SUMIFS formula to find the latest price for Pens. This formula can be used in Excel 2007, or later versions. If you’re using Excel 2003, you can use SUMPRODUCT – see that example in the next section.

NOTE: This solution assumes that a product’s price is never changed twice on the same date.

Here is the SUMIFS formula, in cell C12:

    =SUMIFS($C$2:$C$9,$A$2:$A$9,A12,$B$2:$B$9,B12)

maxiflatestprice03

Excel 2003 Formula

The SUMIFS function isn’t available in Excel 2003, so if you’re using that version, you can use the SUMPRODUCT function instead:

    =SUMPRODUCT(($A$2:$A$9=A12)*($B$2:$B$9=B12)*($C$2:$C$9))

maxiflatestprice04

Download the Sample File

You can download the sample file from the MIN and MAX Functions page on my website.

__________________________

4 comments to Get Latest Price for Specific Product

  • Patrick M

    Well done, Debra!

    I've always done stuff like this with formulas as well, but a PivotTable works well too. Just sort the date field newest to oldest, and use a "Top 10" filter to display only the top 1 items.

  • DILEEP RAO

    Great ! This will save the time of writing a macro. Also some users may not like the (slightly) lower security settings that macros require.

  • Thank's that technique to find the latest date that a product’s price was changed.

  • David

    An alternative for the SUMPRODUCT formula in 2003 is using the SUM function as an array formula (with the same arguments as SUMPRODUCT), i.e. the formula is exactly the same except for "PRODUCT" and is entered with Ctrl+Shift+Enter instead of just enter.

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>