Pull data from another sheet

[Excelski]

Hi,

The attached contains a formula kindly supplied from this site, I have tried to adapt it for my current needs without luck.

I need the result in sheet 2 based on given criteria in sheet 2 from data pulled from sheet 1

I hope the attached will make sense

Any help is greatly appreciated

Kevin

[JohnTopley]

So how did you calculate your expected result off 65.63 ?????

175 is correct based on the formula.

EDIT: spotted calculation on Sheet1 !!!

[JohnTopley]

Try

=SUMPRODUCT((Sheet1!B3:B17>=Sheet2!C3)*(Sheet1!B3:B17<=Sheet2!D3)*(Sheet1!H2:M2=Sheet2!F3)*Sheet1!H3:M17)/Sheet1!O8*SUMPRODUCT((Sheet1!B3:B17>=Sheet2!C3)*(Sheet1!B3:B17<=Sheet2!D3)*Sheet1!D3:F17)

[popipipo]

Is this what you want?

[Excelski]

Thank you John, works great!
Kevin

[JohnTopley]

Removing O8 from previous formula ..

=SUMPRODUCT((Sheet1!B3:B17>=Sheet2!C3)*(Sheet1!B3:B17<=Sheet2!D3)*(Sheet1!H2:M2=Sheet2!F3)*Sheet1!H3:M17)/SUMPRODUCT((Sheet1!B3:B17>=Sheet2!C3)*(Sheet1!B3:B17<=Sheet2!D3)*Sheet1!H3:M17)*SUMPRODUCT((Sheet1!B3:B17>=Sheet2!C3)*(Sheet1!B3:B17<=Sheet2!D3)*Sheet1!D3:F17)

[Excelski]

Even better! I hadn't noticed the use of O8

[Excelski]

Is this what you want?

Sorry I didn't see you post, I wanted the answer on sheet 2 but its very helpful as i can see how its put together

Thanks
Kevin