Lookup value and sum cells

[Jan Hoffmann]

Hello,

I'm stuck with a lookup-problem. I have a table showing monthly sales for customers with "Customer Name" in column A and month in the header row.

So now I want to do a Moving Annual Total lookup function, which for a given month and customer, look up the sales for the given month and accumulate it with the previous 11 month, so that it shows a 12-month total. Eg. typing in Customer Z and the month January 2009, the calculated cell returns the total sale for customer Z in the period February 2008 to January 2009.

I hope you can help me with this problem - Thank you in advance!

Best regards

Jan Hoffmann

 

[Richard Schollar]

Hi Jan

Try:

=SUM(INDEX(DataTable,MATCH(Customer,$A$2:$A$100,0),MATCH(Month,$B$1:$IV$1,0)):INDEX(DataTable,MATCH(Customer,$A$2:$A$100,0),MAX(1,MATCH(Month,$B$1:$IV$1,0)-12)))

Replace ranges/values to suit.

 

[xld]

Is the month stored as a true date, e.g. 01/01/2008, or as text, Jan 2008?

 

[Jan Hoffmann]

Hello guys,

@xld: Yes, it's true dates

@Richard: You've nailed that one - Problem solved - very good and thank you very much!

Best regards

Jan Hoffmann