Lookup values accross several sheets to create summary sheet

[Absed]

I have workbook where I track player stats in our league that concist of several teams. I have entered stats over several season already to my excel. Now for my summary sheet I would need to make excel lookup player names (that may or may not appear on several sheets) and then for example sum up all the goals that player has made in all of those seasons. The same player is on a different cell on different sheets so excel would have to compare the name of the player and then sum up a value that is lets say 5 cells to the right of that players name. I would like to have every player that has played in any of the seasons on the summary sheet.

I added an example workbook as an attachment that similar to the way my real data is laid out. Hopefully that will make it easier to understand what im trying to achieve

[Absed]

Sorry, forgot to add the attachment...

[micope21]

Hi

Please click on attachment.

I have made a change in season 1 and 2 to column A

New page call Sheetlist

Good luck

Cheer

[Absed]

Thank you very much micope! That looked pretty promising but one thing I would like to be able to do is sort the stats. Let's say that I want to sort by goals and now when I try to sort it all of the data disappears... If I can somehow sort the stats then this method would be perfect for me.

<e> Actually if I sort from A to Z it works fine but when I try to sort from Z to A all the data disappears. Any idea what is going on?

[micope21]

Pleas click on attachment

It this what you looking for?

[Absed]

A huge thanks to you micope21!!! I've been making a lot of progress with your help

But now I have a new problem with my summary sheet. Is there a way that using your method I could count the times a value (let's say 5) appears in a column next to the player? So for instance now you made me this formula:

=IF($A2="";"";SUMPRODUCT(SUMIF(INDIRECT("'"&sheetlist&"'!A:A");$A2;INDIRECT("'"&sheetlist&"'!H:H"))))

which sums up the values a player has in Column H. Could there be a similar formula which counts how many times a certain value appears in a cell accross the sheets?

So in the example workbook micope21 made I would like to be able to count the times value 5 apperas in Column E on the season sheets. Is this possible?

[micope21]

Hi

I'm not sure what you trying to say.

To count player in different sheet would be this =IF($B2="";"";SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheetlist&"'!A:A"),B2)). B2 point to Player1.
Better if you would upload sample workbook if this not answer you looking for?

[Absed]

That's not exactly what I was looking for but close to my another problem. I attached a sample workbook to this message where I tried to explain my questions more clearly.

[micope21]

That's not exactly what I was looking for but close to my another problem. I attached a sample workbook to this message where I tried to explain my questions more clearly.

Ok. This formula =IF($A2="","",SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheetlist&"'!A:A"),A2))) is correct.

I notice player10 name 5 different teams in season1 and 5 in season2. Do they allow same player name play in different teams per season? or just 1 player name per season?

If Yes? This formula I give you is correct.
If no? Try different formula.

Let me know

Cheer

[Absed]

That example worksheet is kind of messed up but a player can play for many teams during the season (usually 2 or 3 at most). So same player can play in different team per season. That is the reason the formula won't work for me.

[micope21]

Hi

Have a look workbook.

Season 1 and 2
Column J put in =COUNTIF($A$2:A2,A2)
Column K put in =IF($J2=1,$A2,"")

Then column H. Use this =IF($A2="","",SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheetlist&"'!K:K"),A2)))

Cheer

[Absed]

Thank you! That did exactly what I wanted! You're the best!

Could you or anybody else help me out with my other problem? I also wanted to count how may times a certain value appears for a player in all of his seasons. So in the example worksheet I would like to count how many times the value "5" is found on column E (in the season sheets) for every player. Or as a another example how many times a player has scored lets say more than 3 goals. Is this possible? If you can help me out on this one last thing I hope I can figure out everything else by myself.

I've made wonderful progress thanks to you micope21!

[micope21]

Just to be clear if I got this right?

You looking at Play off in Season Column E or Goals in Season Column F?

You looking at count =5 and over. Not less than 5?

Is that right?

[Absed]

Yes, I'm looking at playoff in Season Column E. Values that are =5.

+

I'm looking at Goals in Season Column F =3 and over.

So two different formulas.

[micope21]

Hi

Season 1 and 2

Column N =IF($E4=5,1,0)
Column O =IF($F4>=3,1,0)

You have a choice
Lower: <
Lower and =: <=
=: =
Higher and =: >=
Higher: >

Then Dummy Column K =IF($B2="","",SUMPRODUCT(SUMIF(INDIRECT("'"&Sheetlist&"'!A:A"),$B2,INDIRECT("'"&Sheetlist&"'!N:N"))))
Column L =IF($B2="","",SUMPRODUCT(SUMIF(INDIRECT("'"&Sheetlist&"'!A:A"),$B2,INDIRECT("'"&Sheetlist&"'!O:O"))))