Sum last X amount of occurrences in a column

[jack109]

Hi
I've tried looking around but I can quite find a formula to meet my needs. What I want is for the formula to sum column B using the last 3 occurrences of a value set in D1 using column A (PERSON).

So for example if I was to use Alice , using the data below the sum of Alice would be 85 (16,49,20).

I would like the formula to work its way up from the last entry as this data will grow

I've posted a table below, had to use the table only method as other option is freezing my excel.

PERSON	AMOUNT
ALICE	11
TREVOR	50
TREVOR	18
BECKY	28
IAN	14
TREVOR	33
DAVID	25
BECKY	45
TREVOR	27
ALICE	20
IAN	5
TREVOR	49
DAVID	17
IAN	47
BECKY	34
BECKY	28
DAVID	17
ALICE	49
BECKY	34
DAVID	38
IAN	17
TREVOR	29
ALICE	16
IAN	42
IAN	48

 

[JvdV]

A good usecase for `TAKE()`:

Book1
	A	B	C	D
1	Person	Amount		ALICE
2	ALICE	11		85
3	TREVOR	50
4	TREVOR	18
5	BECKY	28
6	IAN	14
7	TREVOR	33
8	DAVID	25
9	BECKY	45
10	TREVOR	27
11	ALICE	20
12	IAN	5
13	TREVOR	49
14	DAVID	17
15	IAN	47
16	BECKY	34
17	BECKY	28
18	DAVID	17
19	ALICE	49
20	BECKY	34
21	DAVID	38
22	IAN	17
23	TREVOR	29
24	ALICE	16
25	IAN	42
26	IAN	48
Sheet6

Cell Formulas
Range	Formula
D2	D2	=SUM(TAKE(FILTER(B:B,A:A=D1),-3))

 

[Fluff]

How about

=SUM(TAKE(FILTER(B2:B100,A2:A100=D1),-3))

 

[jack109]

Thanks , one more question could this be adapted so that it would use two criterias eg. column C would now be YES/NO so count the last 3 "Alice" only if "YES" is adjacent in column C??

 

[Fluff]

How about

=SUM(TAKE(FILTER(B2:B100,(A2:A100=D1)*(C2:C100="yes")),-3))

 

[jack109]

Brilliant, thank you very much

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[jack109]

Sorry to be a pain, how would you modify this formula to count instead of Sum. I'd like the formula to Count how many times "YES" appears in the last 3 occurrences of "Alice". I've had a go myself by trying to use COUNT but to no avail.

Cheers

 

[JvdV]

Book1
	A	B	C	D
1	Person	Amount	Val	ALICE
2	ALICE	11	YES	1
3	TREVOR	50	NO
4	TREVOR	18	YES
5	BECKY	28	NO
6	IAN	14	YES
7	TREVOR	33	NO
8	DAVID	25	YES
9	BECKY	45	NO
10	TREVOR	27	YES
11	ALICE	20	NO
12	IAN	5	YES
13	TREVOR	49	NO
14	DAVID	17	YES
15	IAN	47	NO
16	BECKY	34	YES
17	BECKY	28	NO
18	DAVID	17	YES
19	ALICE	49	NO
20	BECKY	34	YES
21	DAVID	38	NO
22	IAN	17	YES
23	TREVOR	29	NO
24	ALICE	16	YES
25	IAN	42	NO
26	IAN	48	YES
Sheet6

Cell Formulas
Range	Formula
D2	D2	=SUM(--(TAKE(FILTER(C:C,A:A=D1),-3)="Yes"))

 

[Fluff]

Another option

=LET(f,TAKE(FILTER(C2:C100,A2:A100=D1,),-3),ROWS(FILTER(f,f="yes")))