SUMIF INDEX MATCH problem

DeeEmmEss

New Member
Joined
Nov 25, 2015
Messages
43
I am trying to sum the total number of player scores (usually 10 players) for a particular team (1 of 60) in a particular week (1-26). This formula will be copied down about 200 rows for both Home and Away Team, across 4 divisions. I'm close, but not close enough!

=SUMIF('All Scores'!$C$9:$C$900,S20,INDEX('All Scores'!E:AD,,MATCH(Q20,'All Scores'!E6:AD6)))
,
=SUMIF('All Scores'![ALL TEAMS],[TEAM TO FIND],INDEX('All Scores'![WEEKLY SCORES],,MATCH([PARTICULAR WEEK],
'All Scores'![WEEK 1-26]))).

Appreciate any help.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Without looking at the data, I would suggest putting a 0 in the MATCH function

=SUMIF('All Scores'!$C$9:$C$900,S20,INDEX('All Scores'!E9:AD900,,MATCH(Q20,'All Scores'!E6:AD6,0)))


 
Upvote 0
Solution
Sorry, didn't work. The formula I now have is =SUMIF(INDEX('All Scores'!e9:ad900,,MATCH(Q20,'All Scores'!E6:AD6,0)),). I shall try and explain a little better.
So on sheet 'All Scores' I have 60 teams each with between 12 and 25 players. The season is 26 games long. Each week 10 players take part on each team and their score is entered elsewhere in the spreadsheet and transferred onto 'All Scores'. I am looking to find all instances of Team A ( 'All Scores' column C, S20 on the sum sheet), find the week I wish to sum ('All Scores' E6:AD6, Q20 on the sum sheet), and the result is in T20. Hope that makes sense.



<tbody>
</tbody>

<colgroup><col span="9"></colgroup><tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top