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]

This?

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

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

Cell Formulas
Range	Formula
D3	=SUMPRODUCT(--($B$2:$B$8>=LARGE($B$2:$B$8,D1)),$B$2:$B$8)+SUMPRODUCT(--(Sheet10!$B$1:$B$7>=LARGE(Sheet10!$B$1:$B$7,5-D1)),Sheet10!$B$1:$B$7)

 

[wideboydixon]

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

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

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

WBD

 

[wideboydixon]

The formula from sheetspread is equally valid provided there are no duplicate values e.g. putting 18 against PM will throw out the formula.

WBD

 

[joeltes]

Both of these worked great as there will be no dups. Thanks for response and knowledge.

How can I add a condition to sum only if C:C=1 and if C:C=0 look at the next row until total is 5 rows?

 

[sheetspread]

What will be in the other column C cells besides 0s and 1s? blanks?

 

[joeltes]

What will be in the other column C cells besides 0s and 1s? blanks?

only 0s and 1s. no blanks

 

[joeltes]

What will be in the other column C cells besides 0s and 1s? blanks?

When using the following, it excludes where C:C=0 from the sum but it doesn't look for another row to fulfill its place in the sum (from the total 5)
=SUMPRODUCT(--(PTRN!$M:$M>=LARGE(PTRN!$M:$M,PTRN!$X$2)),PTRN!$M:$M)+SUMPRODUCT(--($C:$C=1),--($O:$O>=LARGE($O:$O,50-PTRN!$X$2)),$O:$O)

 

[sheetspread]

This?

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	{=SUM(LARGE(IF($C$2:$C$8=1,$B$2:$B$8),ROW(INDIRECT("1:"&$D$1))))+SUM(LARGE(IF(Sheet2!$C$1:$C$7=1,Sheet2!$B$1:$B$7),ROW(INDIRECT("1:"&5-$D$1))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

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

 

[joeltes]

This?

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array 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"]{=SUM(LARGE(IF($C$2:$C$8=1,$B$2:$B$8),ROW(INDIRECT("1:"&$D$1))))+SUM(LARGE(IF(Sheet2!$C$1:$C$7=1,Sheet2!$B$1:$B$7),ROW(INDIRECT("1:"&5-$D$1))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

This returns only the largest value of both sheets, its also ignoring if C=1