How to combine SUMIF, INDEX and MATCH

[BeachRock]

I didn't think this would be so difficult to do... But I've been messing with it for a few hours now and I'm getting nowhere.

This is what I have but it isn't working. There are no problems other than it only returns 0 from IFERROR.

Please Login or Register  to view this content.

This formula is supposed to pull the total number of balls made by a player but it should only add up the totals up to and including the week number located in $G$1 on the same sheet as the formula.

D7 is the name of a person (player) on the sheet where the formula is located in cell F7.

FullWPlStats!$A$1:FullWPlStats!$A$18000 is the range on another sheet where the name of the player will be found; one row for each game played in every match since week 1.

FullWPlStats!$G$1:FullWPlStats!$G$18000 is the range where the number of games won by the player will be found for each game played in every match since week 1.

For instance, if the player made 10 balls per match each week out of 18 weeks, and I want to report on how many balls they had made by week 14, I should get a result of 140. For week 15, the number returned should be 150 and so-on depending on the week chosen at $G$1.

Any ideas? I'm thinking there would need to be a reference to only add values where the week number is less than or equal to the current week in $G$1 but I'm not sure how to write that in the formula.

Thanks for any help you can provide.

[daddylonglegs]

Try this version with SUMIFS

=IF(D7="","",SUMIFS(FullWPlStats!G:G,FullWPlStats!A:A,D7,FullWPlStats!B:B,"<="&G1))

That will sum column G when A matches the player in D7 and week number in B is <= week number in G1

[BeachRock]

Bingo!

Nicely done, daddylonglegs!!

At first I thought it wasn't working because the result was 20 less than I thought it should be. However, I had forgotten about BYE weeks, of which there were 2 in the season. It's perfect.

Thanks very much.