Sum of last 'n' values from two different columns

[chrisb182]

Hi Everyone,

From the below information, I would like to sum the last 'n' values based from the TE columns (K and R) depending on which team I select in another cell.

For example:
I select the team "NO" and want to sum up the last two values --- the formula would sum up 25.46 from Column R and 19.7 from Column K.

Does anyone know how to do this?

I really hope I'm making my request clear.

Cheers,

Chris

 

[Fluff]

How about

=SUM(INDEX(K3:K100,XMATCH(Z2,E3:E100,0,-1)),INDEX(R3:R100,XMATCH(Z2,H3:H100,0,-1)))

Where Z2 is the cell with the team name

 

[chrisb182]

How about

=SUM(INDEX(K3:K100,XMATCH(Z2,E3:E100,0,-1)),INDEX(R3:R100,XMATCH(Z2,H3:H100,0,-1)))

Where Z2 is the cell with the team name

You sir, are on a roll! Hahaha.

Two quick questions though.
1) How would I change the above formula to show, for instance, the last three values?
2) Is there a way to exclude blank cells?

 

[Fluff]

Do you have the Take function?

 

[chrisb182]

Do you have the Take function?

I'm afraid not bud.

 

[Fluff]

Ok, how about

=LET(home,FILTER(K3:K100,(E3:E100=Z2)*(K3:K100<>"")),away,FILTER(R3:R100,(H3:H100=Z2)*(R3:R100<>"")),SUM(INDEX(home,SEQUENCE(MIN(3,ROWS(home)),,ROWS(home),-1)),INDEX(away,SEQUENCE(MIN(3,ROWS(away)),,ROWS(away),-1))))

 

[chrisb182]

It doesn't seem to pulling through the result that I wants. Can I just ask what the 'home' and 'away' functions are for? Am I supposed to change them in the formula?

 

[Fluff]

They are just variables to store the result of the filter functions.

 

[chrisb182]

Ah OK. When I put it in for the team 'ARI' for the last three values, it brings back 59.5 but I was expecting 26 as per this image.

 

[Fluff]

I though you meant the last 3 home & last 3 away.

Can you post some sample data, rather than an image.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.