Sum if it's bottom n values in other sheet

[reagk]

Hi, I want to sum n values in sheet 2, if the same rows in sheet1 it's the bottom n values.
For example:
[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl65, width: 128, colspan: 2"]Sheet 1[/TD]
[TD="width: 64"][/TD]
[TD="class: xl65, width: 128, colspan: 2"]Sheet 2[/TD]
[/TR]
[TR]
[TD="class: xl64"]A[/TD]
[TD="class: xl64, align: right"]8[/TD]
[TD][/TD]
[TD="class: xl64"]A[/TD]
[TD="class: xl64, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl64"]B[/TD]
[TD="class: xl64, align: right"]3[/TD]
[TD][/TD]
[TD="class: xl64"]B[/TD]
[TD="class: xl64, align: right"]15[/TD]
[/TR]
[TR]
[TD="class: xl64"]C[/TD]
[TD="class: xl64, align: right"]2[/TD]
[TD][/TD]
[TD="class: xl64"]C[/TD]
[TD="class: xl64, align: right"]14[/TD]
[/TR]
[TR]
[TD="class: xl64"]D[/TD]
[TD="class: xl64, align: right"]7[/TD]
[TD][/TD]
[TD="class: xl64"]D[/TD]
[TD="class: xl64, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl64"]E[/TD]
[TD="class: xl64, align: right"]10[/TD]
[TD][/TD]
[TD="class: xl64"]E[/TD]
[TD="class: xl64, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl64"]F[/TD]
[TD="class: xl64, align: right"]1[/TD]
[TD][/TD]
[TD="class: xl64"]F[/TD]
[TD="class: xl64, align: right"]20[/TD]
[/TR]
[TR]
[TD="class: xl64"]G[/TD]
[TD="class: xl64, align: right"]4[/TD]
[TD][/TD]
[TD="class: xl64"]G[/TD]
[TD="class: xl64, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl64"]H[/TD]
[TD="class: xl64, align: right"]5[/TD]
[TD][/TD]
[TD="class: xl64"]H[/TD]
[TD="class: xl64, align: right"]6[/TD]
[/TR]
</tbody>[/TABLE]


So in this case, if n=3, the bottom 3 value are F, C, B.
So the formula should sum F,C,B in sheet 2: 20+14+15= 49

Thanks in advance

 

[DanteAmor]

Try this

<b>sheet2</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:80px;" /><col style="width:35px;" /><col style="width:80px;" /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">DATA</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">VALUES</td><td > </td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">n</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">RESULT</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >A</td><td style="text-align:right; ">1</td><td > </td><td style="text-align:right; ">3</td><td style="text-align:right; ">49</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >B</td><td style="text-align:right; ">15</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >C</td><td style="text-align:right; ">14</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >D</td><td style="text-align:right; ">3</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >E</td><td style="text-align:right; ">4</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >F</td><td style="text-align:right; ">20</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >G</td><td style="text-align:right; ">7</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >H</td><td style="text-align:right; ">6</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E2</td><td >=SUMPRODUCT((sheet1!B2:B9<=D2)*(B2:B9))</td></tr></table></td></tr></table> <br /><br />

 

[reagk]

Hi DanteAmor,
Thanks but it's not really working.
The value is not always 3 and changing all the time.

 

[jim may]

Didn't bother to set up Sheet2 )On its page), but maybe...
Copy DOWN F2


Excel 2010

	A	B	C	D	E	F	G	H
Sheet 1	Sheet 2	n = >>
A	A
B	B	Total >>
C	C
D	D
E	E
F	F
G	G
H	H

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: center"]Extraction[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCD5B4]#FCD5B4[/URL] , align: center"]3[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"]15[/TD]
[TD="align: right"]14[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] , align: center"]49[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H3[/TH]
[TD="align: left"]=SUM(F2:F9)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=IF(ROW()-1<=$H$1,OFFSET(E$7,-B7+1,0,1,1),0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Hi DanteAmor,
Thanks but it's not really working.

Describe "not really working" for us. As far as I can tell, Dante's formula should work fine.

The value is not always 3 and changing all the time.

Perhaps the problem is in the way the "3" is specified. Dante assumed it would always be specified in cell D2. How did you want the number to be specified for the formula?

 

[reagk]

Hi Rick, It does works once I specify the value in D2 as =small('sheet1'!b2:b11,3)
Just think there is more beautiful way for this solution.
Thanks

 

[DanteAmor]

Hi DanteAmor,
Thanks but it's not really working.
The value is not always 3 and changing all the time.

I already know that the value is not always 3, so you have the option to put it in cell D2, if you do not want it in a cell, just put the number in the formula:


=SUMPRODUCT((sheet1!B2:B9<=3)*(B2:B9))

or
=SUMPRODUCT((sheet1!B2:B9<=4)*(B2:B9))

Or the number you want.

 

[DanteAmor]

Describe "not really working" for us. As far as I can tell, Dante's formula should work fine.

Perhaps the problem is in the way the "3" is specified. Dante assumed it would always be specified in cell D2. How did you want the number to be specified for the formula?

Thanks Rick Rothstein

for the support, let's wait for him to define where he wants the 3 or how is the most beautiful solution.

 

[Rick Rothstein]

Thanks Rick Rothstein

for the support, let's wait for him to define where he wants the 3 or how is the most beautiful solution.

Maybe all he needs to do is incorporate his SMALL function into your formula and do away with the need for cell D2 altogether...

=SUMPRODUCT((sheet1!B2:B9<=SMALL(sheet1!B2:B11,3))*(B2:B9))

 

[DanteAmor]

Maybe all he needs to do is incorporate his SMALL function into your formula and do away with the need for cell D2 altogether...

=SUMPRODUCT((sheet1!B2:B9<=SMALL(sheet1!B2:B11,3))*(B2:B9))

Oh, that's right, you need the 3 minor numbers, not the ones that are less than 3

How about:

=SUMPRODUCT((sheet1!B2:B9<=SMALL(sheet1!B2:B9,D2))*(B2:B9))