Sum top items between two sheets

[joeltes]

Hi all,

I have two sheets that contain products and the amount sold, I'm trying to sum the total items sold for only 5 items that sold most. Trick is I want to split the 5 items to sum between two sheets, on sheet1 sum only 2 of 5 (value in D1)

[TABLE="width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]# Rows Count[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]PA[/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]PB[/TD]
[TD]45[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]PC[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]PD[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]PE[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]PM[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]PN[/TD]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

then check sheet2 and sum the rest of 5 (in this case 3)

[TABLE="width: 50"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]PF[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]PG[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]PH[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]PI[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]PJ[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]PK[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]PL[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

In this example the results would be, the top 5 items sold a total of 133. note that only sheet1 specifies the amount of rows to sum.

 

[sheetspread]

But 18 and 12 are the largest on sheet 1 where there's a 1 in column c, 33,16,9 are the largest on sheet 2 next to a 1 also?

 

[wideboydixon]

Alternative formula without having to use Ctrl+Shift+Enter:

=SUMPRODUCT(LARGE(($B$2:$B$8)*($C$2:$C$8),ROW(INDIRECT("1:"&$D$1))))+SUMPRODUCT(LARGE((Sheet2!$B$1:$B$7)*(Sheet2!$C$1:$C$7),ROW(INDIRECT("1:"&5-$D$1))))

WBD

 

[joeltes]

Alternative formula without having to use Ctrl+Shift+Enter:

=SUMPRODUCT(LARGE(($B$2:$B$8)*($C$2:$C$8),ROW(INDIRECT("1:"&$D$1))))+SUMPRODUCT(LARGE((Sheet2!$B$1:$B$7)*(Sheet2!$C$1:$C$7),ROW(INDIRECT("1:"&5-$D$1))))

WBD

When applying it as follows, x C:C throws an error, any clues on why?
=SUMPRODUCT(LARGE(PTRN!$M:$M,ROW(INDIRECT("1:"&PTRN!$X$2))))+SUMPRODUCT(LARGE(($O:$O)*($C:$C),ROW(INDIRECT("1:"&50-PTRN!$X$2))))

 

[sheetspread]

Don't use the whole column, thats over a million rows in Excel 2007 or later. Even the 65k in earlier versions is outrageous. Does x mean you're using an x instead of 0/1 or you wrote as an example?

 

[joeltes]

Don't use the whole column, thats over a million rows in Excel 2007 or later. Even the 65k in earlier versions is outrageous. Does x mean you're using an x instead of 0/1 or you wrote as an example?

By x I meant multiplication

 

[sheetspread]

Excel 2010
	A	B	C	D	E
1			# Rows Count	2
2	PA	15	0
3	PB	45	0		88
4	PC	7	1
5	PD	12	1
6	PE	2	0
7	PM	6	0
8	PN	18	1
Sheet1

Cell Formulas
Range	Formula
E3	=SUMPRODUCT(LARGE(($B$2:$B$8)*($C$2:$C$8),ROW(INDIRECT("1:"&$D$1))))+SUMPRODUCT(LARGE((Sheet2!$B$1:$B$7)*(Sheet2!$C$1:$C$7),ROW(INDIRECT("1:"&5-$D$1))))

Excel 2010
	A	B	C
1	PF	21	0
2	PG	5	0
3	PH	9	1
4	PI	16	1
5	PJ	8	0
6	PK	33	1
7	PL	2	1
Sheet2

works in my test, but it's not an exact copy of your data

 

[joeltes]

Excel 2010


[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E3[/TH]
[TD="align: left"]=SUMPRODUCT(LARGE(($B$2:$B$8)*($C$2:$C$8),ROW(INDIRECT("1:"&$D$1))))+SUMPRODUCT(LARGE((Sheet2!$B$1:$B$7)*(Sheet2!$C$1:$C$7),ROW(INDIRECT("1:"&5-$D$1))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

This worked perfectly once I specified the range rather than looking at the whole column. Thanks much!