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.

 

[Eric W]

Something like this maybe?

Excel 2010

	A	B	C	D	E
1	Name	Value
2	A	7
3	B	8
4	B	9
5	B	0
6	A	-5
7	A	3
8	A	2
9	C	15
10	C	68
11	C	9
12	B	6
13	A	5
14	A	5	Name	Last how many	Sum
15	A	5	B	3	15
16

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet8​

Array Formulas Cell Formula E15 {=SUMIF(OFFSET($A:$A,LARGE(IF($A$2:$A$15=C15,ROW($A$2:$A$15)),D15)-1,0,1,1):$A$15,C15,OFFSET($B:$B,LARGE(IF($A$2:$A$15=C15,ROW($A$2:$A$15)),D15)-1,0,1,1):$B$15)} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[DRSteele]

This looks awkward but it does do the job.

	A	B	C	D	E
1	name	value		criteria	total last 3
2	A	7		A	15
3	B	8
4	B	9
5	B	0
6	A	-5
7	A	3
8	A	2
9	C	15
10	C	68
11	C	9
12	B	6
13	A	5
14	A	5
15	A	5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula E2 =SUMPRODUCT(((ROW(A2:A15)-ROW(A2)+1)*(A2:A15=D2)=(AGGREGATE(14,6,(ROW(A2:A15)-ROW(A2)+1)/(A2:A15=D2),{3,2,1})))*(B2:B15)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Rick Rothstein]

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.

This (normally entered) formula appears to do what you want...

=SUMPRODUCT((A2:A15="A")*(ROW(2:15)=LARGE((A2:A15="A")*ROW(2:15),{1,2,3}))*B2:B15)

The two red letter A's is the name you are looking up... the blue 1,2,3 (inside the curly braces is what controls how many values will be added (if you wanted 5, then change it to 1,2,3,4,5)... of course you would change all of the 15's to the maximum row number for the cell containing your last piece of data. Oh, in case there are less names in the list than the number of cells you want to add (for example, name "C" only appears twice in the list and you have asked to sum three values), the formula will add what is there (for example, 83 for name "C").

 

[DRSteele]

Oooo, ya. Use this one. Mine's all too complicated. Keep in mind that in both formulas the {1,2,3} construction must be with commas and not with semi-colons.

 

[MindTheGap]

This (normally entered) formula appears to do what you want...

=SUMPRODUCT((A2:A15="A")*(ROW(2:15)=LARGE((A2:A15="A")*ROW(2:15),{1,2,3}))*B2:B15)

The two red letter A's is the name you are looking up... the blue 1,2,3 (inside the curly braces is what controls how many values will be added (if you wanted 5, then change it to 1,2,3,4,5)... of course you would change all of the 15's to the maximum row number for the cell containing your last piece of data. Oh, in case there are less names in the list than the number of cells you want to add (for example, name "C" only appears twice in the list and you have asked to sum three values), the formula will add what is there (for example, 83 for name "C").

Thank you very much.
If I wanted to have this formula on every row customized could I replace the A2:A15 parts with A2:ADDRESS(ROW(A15),COLUMN(A15)) ?

 

[MindTheGap]

Or like this for example except it's not working

=SUMPRODUCT(($A$2:A15=A15)*(ROW(2:ROW(A15))=LARGE(($A$2:A15=A15)*ROW(2:ROW(A15)),{1,2,3}))*$B$2:B15)

 

[Rick Rothstein]

This (normally entered) formula appears to do what you want...

=SUMPRODUCT((A2:A15="A")*(ROW(2:15)=LARGE((A2:A15="A")*ROW(2:15),{1,2,3}))*B2:B15)

The two red letter A's is the name you are looking up... the blue 1,2,3 (inside the curly braces is what controls how many values will be added (if you wanted 5, then change it to 1,2,3,4,5)... of course you would change all of the 15's to the maximum row number for the cell containing your last piece of data. Oh, in case there are less names in the list than the number of cells you want to add (for example, name "C" only appears twice in the list and you have asked to sum three values), the formula will add what is there (for example, 83 for name "C").

Thank you very much.
If I wanted to have this formula on every row customized could I replace the A2:A15 parts with A2:ADDRESS(ROW(A15),COLUMN(A15)) ?

Or like this for example except it's not working

=SUMPRODUCT(($A$2:A15=A15)*(ROW(2:ROW(A15))=LARGE(($A$2:A15=A15)*ROW(2:ROW(A15)),{1,2,3}))*$B$2:B15)

I am sorry, but I am having trouble understanding what functionality you are trying to implement here. Can you show a before and after example that demonstrates what you are trying to accomplish?

 

[MindTheGap]

 

[Rick Rothstein]

I am sorry, but that is still not clear. It looks like you are saying the last 3 C's equals 1 (it doesn't), the last 3 D's equals 5 (it doesn't), the last 3 A's = 5 and (this is where it gets even more unclear) that the last three B's equals either 7 or 10 (neither is correct).