Formula to calculate the data of the last order

[khaled]

A B C D
1 P O Date Code Kgs $
2 01-Jan-08 R 1 10
3 03-Feb-08 F 2 20
4 07-Mar-08 X 3 30
5 10-Mar-08 Z 10 40
6 27-Apr-08 G 5 50
7 01-May-08 Z 6 60

For code Z, I need a formula to calculate the $ of the last order.
Z of course can have several dates.

 

[Sheeloo]

Enter the following in E2
=SUMPRODUCT(--(A2:A7=MAX(IF(B2:B7=B2,A2:A7,""))),--(B2:B7=B2),D27)
and press CTRL-SHIFT-ENTER
and copy down

This will give the $ amount for the latest date for the code in Col B

If you just want for code "z" then use this
=SUMPRODUCT(--(A2:A7=MAX(IF(B2:B7="Z",A2:A7,""))),--(B2:B7="Z"),D27)
Don't forget to press CTRL-SHIFT-ENTER

Replace 7 in A7, B7 etc by the last row number of your data set