Hi,

I would use Bernard's approach, but if you don't want those extra columns in

the other sheets:

Since you only have 4 sheets you can copy and paste the function:

=SUMPRODUCT(--($A2=Sheet2!$A$2:$A$25),--(Summary!$B2=Sheet2!$B$2:$B$25),--(Summary!$C2=Sheet2!$C$2:$C$25),Sheet2!D$2

$25)

into one cell 4 times with a + between each and then change the sheet names

accordingly to give something like this:

=SUMPRODUCT(--($A2=Sheet2!$A$2:$A$25),--(Summary!$B2=Sheet2!$B$2:$B$25),--(Summary!$C2=Sheet2!$C$2:$C$25),Sheet2!D$2

$25)+SUMPRODUCT(--($A2=Sheet3!$A$2:$A$25),--(Summary!$B2=Sheet3!$B$2:$B$25),--(Summary!$C2=Sheet3!$C$2:$C$25),Sheet3!D$2

$25)+SUMPRODUCT(--($A2=Sheet4!$A$2:$A$25),--(Summary!$B2=Sheet4!$B$2:$B$25),--(Summary!$C2=Sheet4!$C$2:$C$25),Sheet4!D$2

$25)+SUMPRODUCT(--($A2=Sheet5!$A$2:$A$25),--(Summary!$B2=Sheet5!$B$2:$B$25),--(Summary!$C2=Sheet5!$C$2:$C$25),Sheet5!D$2

$25)

My choice would be to write a custom VBA function.

--

If this helps, please click the Yes button.

Cheers,

Shane Devenshire

"Junaid" wrote:

> I have one workbook with several sheets, i want to apply the sumproduct

> function.

> my file contains 4 week sheets & 1 monthly summary sheet on summary sheet i

> want the sum of each product.

>

> A B C D E

> 1 Name Size Brand Planned Produced

> 2 XXX 125x24 AAA 10000 8000

> 3 YYY 125x24 AAA 5000 4500

> 4 XXX 250x24 BBB 10000 9000

>

> I want to add the planned & Produced columns if the criteria is matched.

> range for all sheets is same 25 rows in each sheet.