Sum the last n values in a row based on a criteria.

[MindTheGap]

Hello.

I have Column A and Column B and I am looking to get the sum of the last 3 values from column B given they match the name in A. The formula I am currently using returns the sum of the last 3 values of B but when I try and put that formula in a SUMIFS function to test for matching the name in column A it doesn't work.

Thank you in advance.

 

[MindTheGap]

Sorry it wasn't clear my bad.

 

[Marcelo Branco]

This?

	A​	B​	C​
1​	name​	value​
2​	A​	1​
3​	B​	1​
4​	B​	9​
5​	B​	2​
6​	A​	-5​
7​	A​	3​
8​	A​	2​
9​	C​	15​
10​	C​	68​
11​	C​	9​
12​	C​	1​	78​
13​	D​	5​	5​
14​	A​	5​	10​
15​	B​	7​	18​

Formula in B12 copied down
=SUMPRODUCT(--(ROW(A$2:A12)>=LARGE((A$2:$A12=A12)*ROW(A$2:$A12),3)),--(A$2:A12=A12),B$2:B12)

Hope this helps

M.

 

[MindTheGap]

Thank you very much. What do I need to read to completely understand what's going on in that formula?

 

[Marcelo Branco]

You are welcome.

An excellent article about SUMPRODUCT
http://xldynamic.com/source/xld.SUMPRODUCT.html

M.