Search code examples
google-sheetsformulasumifs

SUM last N values with criteria

I have simple table that looks like this:

enter image description here

All i need is to SUM points for specific player (John) in his last 3 matches.

I was able to come with this formula: 

SUMPRODUCT(LARGE((A2:B="John")*(C2:D);{1;2;3}))

The problem is that instead of what I was looking for, it sums the highest 3 values, that can be anywhere in that range.

Is there some similar formula, that can do only the last 3 matches?

Solution

  • I think a SUMPRODUCT can get you there with some constructed arrays using a COUNTIFS() and ROW() to get the most recent 3.

    This formula:

    =SUMPRODUCT((COUNTIFS(A:B,G2,ROW(A:B)*{1,1},">="&ROW(A:B)*{1,1})<=3)*(A:B=G2),C:D)

    on this sheet I made seems to work.