Find values in a range and Sum only the last N

[IvanMarkus]

Hi Community!


1-I want to find values in a range that is equal to one cell.
2-Sum the values of the another column that matches with found. But I just want to add the N (4,5,6, ...) last.


With the function SUMIF I get to do the first part, but I add up all the values found.
SUMAIF($B$2:$B137,B138,$C$2:$C137)


I need sum only the last 6 values found.


I do not know how to control the number of summands max to include in the SUM function.


Any suggestions?

Date	ColumnaA	Importe
09/12/2000	A23000	456
09/12/2000	B34000	789
09/12/2000	H25000	123
09/12/2000	O12000	258
09/12/2000	T47000	147
09/12/2000	R45678	369
09/12/2000	HU8900	963
09/12/2000	KP4098	852
10/12/2000	TR3409	741
10/12/2000	E35000	456
16/12/2000	A23000	654
16/12/2000	B34000	987
16/12/2000	E35000	321
16/12/2000	H25000	456
16/12/2000	HU8900	789
16/12/2000	KP4098	123
16/12/2000	O12000	258
16/12/2000	R45678	147
17/12/2000	T47000	369
18/12/2000	TR3409	963
22/12/2000	B34000	852
23/12/2000	HU8900	741
23/12/2000	KP4098	456
23/12/2000	T47000	654
23/12/2000	TR3409	987
23/12/2000	O12000	321
23/12/2000	R45678	456
23/12/2000	H25000	789
23/12/2000	A23000	123
23/12/2000	E35000	258
26/12/2000	TR3409	147
26/12/2000	T47000	369
26/12/2000	R45678	963
26/12/2000	O12000	852
26/12/2000	KP4098	741
26/12/2000	HU8900	456
26/12/2000	H25000	654
26/12/2000	E35000	987
26/12/2000	B34000	321
27/12/2000	A23000	456
30/12/2000	A23000	789
30/12/2000	B34000	123
30/12/2000	H25000	258
30/12/2000	O12000	147
30/12/2000	T47000	369
30/12/2000	R45678	963
01/01/2001	HU8900	852
01/01/2001	KP4098	741
01/01/2001	TR3409	456
01/01/2001	E35000	654
01/01/2001	A23000	987
01/01/2001	B34000	321
01/01/2001	E35000	456
01/01/2001	H25000	789
01/01/2001	HU8900	123
02/01/2001	KP4098	258
13/01/2001	O12000	147
13/01/2001	R45678	369
13/01/2001	T47000	963
13/01/2001	TR3409	852
13/01/2001	B34000	741
13/01/2001	HU8900	456
13/01/2001	KP4098	654
13/01/2001	T47000	987
13/01/2001	TR3409	321
14/01/2001	O12000	456
20/01/2001	R45678	789
20/01/2001	H25000	123
20/01/2001	A23000	258
20/01/2001	E35000	147
20/01/2001	TR3409	369
20/01/2001	T47000	963
20/01/2001	R45678	852
20/01/2001	O12000	741
21/01/2001	KP4098	456
22/01/2001	HU8900	654
24/01/2001	H25000	987
30/01/2001	E35000	321
30/01/2001	B34000	456
31/01/2001	A23000	789
31/01/2001	B34000	123
31/01/2001	H25000	258
31/01/2001	O12000	147
31/01/2001	T47000	369
31/01/2001	R45678	963
31/01/2001	HU8900	852
03/02/2001	KP4098	741
03/02/2001	TR3409	456
03/02/2001	E35000	654
03/02/2001	A23000	987
03/02/2001	B34000	321
03/02/2001	E35000	456
03/02/2001	H25000	789
03/02/2001	HU8900	123
03/02/2001	KP4098	258
07/02/2001	O12000	147
10/02/2001	R45678	369
10/02/2001	T47000	963
10/02/2001	TR3409	852
10/02/2001	B34000	741
10/02/2001	HU8900	456
10/02/2001	KP4098	654
10/02/2001	T47000	987
10/02/2001	TR3409	321
11/02/2001	O12000	456
12/02/2001	R45678	789
24/02/2001	H25000	123
24/02/2001	A23000	258
24/02/2001	E35000	147
24/02/2001	TR3409	369
24/02/2001	T47000	963
24/02/2001	R45678	48
24/02/2001	O12000	741
24/02/2001	KP4098	456
25/02/2001	HU8900	654
03/03/2001	H25000	987
03/03/2001	E35000	321
03/03/2001	B34000	456
03/03/2001	A23000	789
03/03/2001	A23000	123
03/03/2001	B34000	258
03/03/2001	H25000	147
03/03/2001	O12000	369
04/03/2001	T47000	963
05/03/2001	R45678	852
07/03/2001	HU8900	741
10/03/2001	KP4098	456
17/03/2001	TR3409	654
17/03/2001	E35000	987
17/03/2001	A23000	321
17/03/2001	B34000	456
17/03/2001	E35000	789
17/03/2001	H25000	123
17/03/2001	HU8900	258
17/03/2001	KP4098	147
18/03/2001	O12000	369
18/03/2001	R45678	963

<colgroup><col width="80" style="width:60pt"> <col width="80" style="width:60pt"> <col width="80" style="width:60pt"> </colgroup><tbody>
</tbody>

​

 

[Domenic]

To sum the last 6 values based on a condition, where E2 contains the value of interest (ie. A23000), try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(ROW($B$2:$B$138)>=LARGE(IF($B$2:$B$138=E2,ROW($B$2:$B$138)),6),IF($B$2:$B$138=E2,$C$2:$C$138)))

Note, the formula returns the error value #NUM! when there are less than 6 values that meet the condition.

Hope this helps!

 

[IvanMarkus]

It works perfect.

Thank you very much.

 

[Domenic]

You're very welcome!