Combining Lookup and SUM

[geoffbird]

I am having trouble trying to workout how to do the below.

W/E	12/04/2014	19/04/2014	26/04/2014	03/05/2014	10/05/2014	17/05/2014	24/05/2014
Rev.	200	200	200	200	200	200	200
Cost.	100	100	100	100	100	100	100

<tbody>
</tbody>

I would like to combine a LOOKUP and SUM to say the following
Lookup a date in cell A1 within this table and sum Rev. from that date to column at end of the spreadsheet,


Hope this makes sense, Any help would be very much appreciated!!!!

 

[Special-K99]

Perhaps (untested)

=SUMPRODUCT((Sheet1!A1<=Sheet2!B1:Sheet2!H1)*(Sheet2!B2:H2))

UPDATE: This seems to work.