Using Sum with Large to create form tables for results

[agorman83]

Hi everyone, I hope you can help me. I have used Sum with Large to find data which matches 5 game and 10 game form from individual data for matchdays. However, it doesn't work consistently and I don't know why. I am sure it is a problem I have caused.

Could someone have a look at the table for me and see what I have done wrong?

The formula I have used is:

=SUM(LARGE(IF($D$4:$D244=$D244;L$4:L244);{1;2;3;4;5}))

I am looking to retrieve the 5 most recent results to that specific gameday. It looked like it worked at first as in the first gameday it matched what it should have been. However, as it goes along it doesnt match what it should be.

I appreciate any help you can give on this.

Thank you.

 

[bosco_yip]

1] Try to use this array (CSE) formula :

=SUM(LARGE(IF($D$4:$D244=$D244,L$4:L244),{1;2;3;4;5}))

Confirmed in enter with by pressing Ctrl+Shift+Enter 3 keystrokes together.

2] Or, this normal formula :

=SUMPRODUCT(AGGREGATE(14,6,L$4:L244/($D$4:$D244=$D244),{1;2;3;4;5}))

Regards

 

[agorman83]

=SUMPRODUCT(AGGREGATE(14,6,L$4:L244/($D$4:$D244=$D244),{1;2;3;4;5}))

Okay I think the formula works well, I think the problem is with the logic I have used. I need it to calculate the most recent 5 games based on the GD column (G:G) then for it to sum another column based on that.

For example:

Use $G$4:$G244 to find the 5 most recent gamedays based on $D$4:$D244=$D244 & $I$4:$I244<>"NG" then to sum L$4:L244 based on that criteria.

Is it possible with the existing formula?

Thanks for your help