Sum last X amount of occurrences in a column

jack109

Board Regular
Joined
May 10, 2020
Messages
50
Office Version
  1. 365
Platform
  1. Windows
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.

PERSONAMOUNT
ALICE11
TREVOR50
TREVOR18
BECKY28
IAN14
TREVOR33
DAVID25
BECKY45
TREVOR27
ALICE20
IAN5
TREVOR49
DAVID17
IAN47
BECKY34
BECKY28
DAVID17
ALICE49
BECKY34
DAVID38
IAN17
TREVOR29
ALICE16
IAN42
IAN48
 
Another option
Excel Formula:
=LET(f,TAKE(FILTER(C2:C100,A2:A100=D1,),-3),ROWS(FILTER(f,f="yes")))
Thought of that, but if there wouldn't be any 'Yes', this would return a false '1'.
 
Upvote 0

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
No it won't it will return a #calc error which can be handled like
Excel Formula:
=LET(f,TAKE(FILTER(C2:C100,A2:A100=D1,),-3),IFERROR(ROWS(FILTER(f,f="yes")),0))
 
Upvote 0
No it won't it will return a #calc error which can be handled like
Excel Formula:
=LET(f,TAKE(FILTER(C2:C100,A2:A100=D1,),-3),IFERROR(ROWS(FILTER(f,f="yes")),0))
You are correct, I usually see people use `COUNTA()` instead of `ROWS()` which does what I explained.
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top