Showing latest date in Pivot chart with total for all dates

[Ellie]

Hi all

I don't know whether this is possible or not, as I'm not overly
knowledgeable on Pivot charts.

My pivot chart has a product ID (col A), BBE dates (col B) and total cases
for each BBE (col C).

I would like the pivot chart to show the latest date only, but in the
process showing the total cases for all dates against that product ID, if
more than 1 date applies to the product ID.

e.g. currently shown on pivot chart as below:
000301 17/06/2010 45
31/01/2012 193
000702 30/11/2011 2445
1000456 25/06/2010 9
31/01/2011 75
31/03/2011 45
30/06/2011 145
would like to see as follows, if possible:
000301 31/01/2012 238
000702 30/11/2011 2445
1000456 30/06/2011 274

Many thanks in advance for any help or advice.

 

[David]

If your data is sorted by product and decending date-if not record a simple
macro to do it- you could create a new column "Most recent date"- do a
Vlookup using the product ID (which will be there for every row)- this will
then give the the latest date accross from what every other dates they were
sold- then us this column in you pivot table