Associating a list with positve and negative numbers formula

**damianberry** · 11-13-2012, 05:58 AM

Here I am with yet another problem with my fantasy football league records (I've had a lot of much appreciated help from patient people on here. Thanks to those of you who have contributed your time for me on this).

Our league is composed of two divisions of 5 teams each. Therefore, each player has 4 opponents within their own division they play against during the season. I am trying to compile a won-loss-tie total against these teams within a division.

I have attached a condensed sample of our worksheet layout. Mike N plays against the other 4 teams that are highlighted in yellow to the left. I want to therefore only tally the wins, losses and ties versus those teams (which I’ve also highlighted in yellow in the row).

Row 30 has positive and negative numbers. The positive numbers are wins, the negatives are losses, and the zero is for a tie (rare, but possible chance of happening).

With the numbers I’ve provided, there would be one win, one loss and one tie against Mike N’s division opponents.

Is there a way of doing this?

Thanks,
Damian

**ChemistB** · 11-13-2012, 10:45 AM

Is there any other way to tell that E30, H30 and I30 were Mikes opponents besides color? Color is not data and you would need VBA (I believe) to look at the cells background formatting to pull out a specific color.

See the formula in C30 (I added the information in Row 13.

**damianberry** · 11-15-2012, 12:37 AM

It isn't working as you have it. I only have the divisions separated based on a list to the left. I have only highlighted the two divisions in different colors to help you see which teams belong to which divisions. The colors aren't automatically associated with each team.

I've tried to change the scores, players, etc to test and it doesn't seem to work.

**mahju** · 11-15-2012, 01:35 PM

Hi
If you have separate table of score for each player then try this

In A30 type 1 for count of score of first group and 2 for second group.

Or if you want separate formulae then
Replace "A30" with '1' for first group
Replace "A30" with '2' for second group

and no need of A30

If it is not what you want please explain more with examples
Regards

**damianberry** · 11-16-2012, 01:30 AM

Mahju,

You're very close to what I need but I don't think it is exactly how I need it to work. In row E14:J14 (and continuing on to the right) I want to put the opponent (from a drop down list, which I have already created in my master sheet I'm actually using).

The example uses Mike's results. Mike is from the yellow division, so I want the wins/losses against only those opponents (the other 4 in yellow) to automatically be totaled.

The way you have it seems to work, but not if I continue to add more results in columns to the right.

**mahju** · 11-16-2012, 08:20 AM

Hi

You did not explain where the second player should be.

Any way formula in E32 can be copied to right as needed.

The formula in C32 needs to be changed according to the range your data spans to.

For example

=COUNTIF(E32:CZ32,"Won") & "-" & COUNTIF(E32:CZ32,"Loss") & "-" &COUNTIF(E32:CZ32,"Tie")

Will calculate the data from E32 to CZ32.

If you have a vertical list of all the teams, you can use vlookup to fill the cell A30 according to the group it belongs to or Hlookup if the list is horizontal.

If range E14 to J13 and to right should always contains all the names of the teams then you can use Index / Match function to auto populate the cell A30 according to the group it belongs to.