Combining Lookup and SUM

[geoffbird]

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


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]W/E[/TD]
[TD]12/04/2014[/TD]
[TD]19/04/2014[/TD]
[TD]26/04/2014[/TD]
[TD]03/05/2014[/TD]
[TD]10/05/2014[/TD]
[TD]17/05/2014[/TD]
[TD]24/05/2014[/TD]
[/TR]
[TR]
[TD]Rev.[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]Cost.[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]

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.