Using sumif to show win loss when 1 team plays 4 others

Earlier today I submitted a problem where I had to show win loss results where 1 team played another in a fixture list. That was solved brilliantly using sumif. Now 1 team has to play 4 others and I need to show the results in a win and loss cell. I hope that it can be solved in a simular way as the previous problem.

Try these:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

Hello Laurence1965

This is effectively a follow-up to your original query so I've merged these threads - this makes it easier to see what has been suggested so far - thanks

With regard to the question I'm not sure what you mean by "Jacksonville only need to play Atlanta & Carolina". The listed games show Jacksonville playing all four teams - if they only played Atlanta and Carolina you'd only get results for those 2....or if you only listed those 2 in table 4 then you'd only get results for those 2 - for the setup you have what determines that you only want results for Jacksonville playing those teams?

I am not sure I understand.. you still listed all 4 teams they play... so how do you know that you only want Carolina and Atlanta.

If you remove New Orleans and Tampa from table 3, you get the results you show in Table 4

is that what you are needing? If not, please elaborate.

Can you show a more representative example of what you mean.. especially by:

Yesterday I needed the results when Jacksonville (for example) played a complete division which were naturally grouped together so the lookup array covered the group eg $b$37:$b$40 . Today they need to play 2 other teams that are located in different divisions whose cells are not next to each other in the list.

I will try and explain what I want from the code. The "win" code from yesterday is below

Please Login or Register  to view this content.

Everything else can stay the same but instead of the lookup array $B$37:$B$40 which covers 4 cells I need to pick out only 2 other cells. Lets say for example I just want the win results from $B$37 and $B$40 leaving out the 2 cells in between how would the code have to be altered. I've tried taking out the colon and just putting in a comma (as below) but that doesn't work.

Please Login or Register  to view this content.

Is that any clearer?

Maybe, you can add "x" in A37:A40 for the teams you want to count in...

and then change formulas in C42 and D42 to:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

each of these formulas need to be confirmed with CTRL+SHIFT+ENTER not just ENTER to work.

Now you just put an "x" beside the team(s) in B37:B40 that you want to count in the wins/losses.

Will that work for you?

Hit Reply and click the paperclip icon.

If it is too large, then you can zip it first.

So in Column AZ and BA, for all teams in column B you want to see the wins/losses against those specific 2 teams (Pittsburgh and Indianapolis) only?

Maybe I am just dense or missing some clue... but how do you know which two teams for each team in column B? Or does it matter the teams? If not, then what am I obviously missing here? How does one know which to count?

Somehow we need to extract the 2 teams for each... will you enter these manually in each row, and then we can use those or do we need to know the exact logic so we can try a formula to extract them. I am not a football fan, so I don't know how it all works. You will have to explicitly outline the rules.

Will BB and BC have the 2 teams in them? If so, then yeah, that's what we need.