Sumif ,but only upto where last 3 instances match

[ste33uka]

Hi i have added a imige for better understanding,
Would anyone have a formula to sum column C,
when G1 matches column A and H1 matches column B, but only the last 3 instances where they match.
So in example answer would be 14.
Thanks

 

[ste33uka]

This one works

=SUMPRODUCT((ROW(A5:A21)=IFERROR(AGGREGATE(14,6,ROW(A5:A21)/(A5:A21=G1)/(B5:B21=H1),{1,2,3}),0))*C5:C21)

Boom! thanks alot ,really appreciate it

 

[ste33uka]

This one works

=SUMPRODUCT((ROW(A5:A21)=IFERROR(AGGREGATE(14,6,ROW(A5:A21)/(A5:A21=G1)/(B5:B21=H1),{1,2,3}),0))*C5:C21)

Hi would you have a formula for countif ,that would only count upto last 3 where possible for amount of times apple and mark match ?

 

[jasonb75]

Try

=SUMPRODUCT((ROW(A5:A21)=IFERROR(AGGREGATE(14,6,ROW(A5:A21)/(A5:A21=G1)/(B5:B21=H1),{1,2,3}),0))*1)

edit:- I just edited the original formula without thinking about it, as soon as I clicked the button to post it, the simple version hit me

=MIN(COUNTIFS(A5:A21,G1,B5:B21,H1),3)

 

[ste33uka]

Try

=SUMPRODUCT((ROW(A5:A21)=IFERROR(AGGREGATE(14,6,ROW(A5:A21)/(A5:A21=G1)/(B5:B21=H1),{1,2,3}),0))*1)

edit:- I just edited the original formula without thinking about it, as soon as I clicked the button to post it, the simple version hit me

=MIN(COUNTIFS(A5:A21,G1,B5:B21,H1),3)

Thank you