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

 

[chrisb182]

Do you reckon it's because of the Excel version that I have?

 

[chrisb182]

Do you reckon it's because of the Excel version that I have? View attachment 79372

Ahhhhh ---- I think I found out why.

There are blank cells between Row166 - Row274 (as there are still results to be inputted).

When I replace 274 with 166, the formula works.

I'm guessing there is no way to make the formula exclude blank cells?

 

[Fluff]

How about

=LET(h,FILTER($C$3:$I$1000,($E$3:$E$1000=X3)*($I$3:$I$1000<>"")),a,FILTER($C$3:$P$1000,($G$3:$G$1000=X3)*($P$3:$P$1000<>"")),r,ROWS(h),s,SEQUENCE(r+ROWS(a)),SUM(INDEX(SORT(IF(s<=r,INDEX(h,s,{1,7}),INDEX(a,s-r,{1,14})),1,-1),{1;2;3},2)))

 

[chrisb182]

That is spot on bud!

Thank you SOOOOO much. I don't understand how quickly you come up with these solutions. Hahaha.

 

[Fluff]

Glad to help & thanks for the feedback.