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)

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

<tbody>
</tbody>

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

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

<tbody>
</tbody>

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

Worksheet Formulas Cell 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)))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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