SOLVED

Trying to write a formula for last X by date

Copper Contributor

Trying to write a formula for last X by date

I'm trying to write a formula to show the sum of the previous 2 dates summed scores as per column D without manually summing each time. Can't get my head around how to use sumifs to do so.

Any help, grateful

3 Replies

Re: Trying to write a formula for last X by date

"previous 2 dates" in relation to what?

Re: Trying to write a formula for last X by date

So as per the calc in column D.

Here I have summed the Teams previous 2 scores based by date:

Cell D6 is the sum of C2 and C4, the last 2 entries by date for this team. This is what I want the formula to calculate, the sum of the last 2 scores based on the previous 2 dates.

best response confirmed by MJWoods (Copper Contributor)
Solution

Re: Trying to write a formula for last X by date

I have solved this with FILTER() and SUM().

``=LET(list,TAKE(CHOOSECOLS(SORT(FILTER(Table[[Date]:[Score]],([Team]=[@Team])*([Date]<[@Date])),1),3),-2),IF(COUNT(list)<2,"",SUM(list)))``

Hope that fits.

1 best response

Accepted Solutions
best response confirmed by MJWoods (Copper Contributor)
Solution

Re: Trying to write a formula for last X by date

I have solved this with FILTER() and SUM().

``=LET(list,TAKE(CHOOSECOLS(SORT(FILTER(Table[[Date]:[Score]],([Team]=[@Team])*([Date]<[@Date])),1),3),-2),IF(COUNT(list)<2,"",SUM(list)))``

Hope that fits.