conditional formatting with totals

if in col Y is a l then it will be red and a w it will be green. this is manually entered in col Y as the games are played. as for the players in col a are the players names and they are set in alpha order and this does not change. as u can see in col. u i tried a =RIGHT(B2,3) but having issues getting just the number extracted. I need to have a total points won and a total points lost in cols V and W so in V the header would be points lost ans in Col.W Points Won.. Hope you can follow this lol

thanks for the help some worked ok.. i am enclosing the sheet.

what needs to happen is it should look like the image i have included. somehow i need it to look at the team names and fill every instance of that team in the grid red for loss and green for win. Then total lets use player Adam will total how many points he lost by totaling the points in red and also total how many he won points in green. so the sheet will look something like the one at the bottom of the sheet by the end of the day.

I was thinking is there a way to index the teams and then have it match the games and if Col Y is a l then color that cell red and if Y is a w then Color it green?

hello

I have updated the inclosed sheet and am close to a solution. here is what I am thinking..

1. I need a conditional format to happen in the "games" table T2:AU22 if you look at the conditional format I tried u will see what I need. ( needs to look at the "TEAMS" BM2:BM33 as well as BN2:BN33 and color cells accordingly using the "l" and "w" as the key.
2. The points that have a "l" designation need totaled for each person in column BD and the points that have a "w" designation need totaled for each person in column BE.

I have modified a formula suggested with no results any help would be so helpful.
I have defined names of "games" and "teams"

OK I have re-vamped the sheet to show what I am trying to get it to do. This was all done manually and I would like to automate it. I am wanting to control the entire sheet on if a NFL team wins or looses. if you notice in Col. BN is a list of all the teams and has been named "teams" in BO is the input column for wins and losses. I have named the table from A2:AU22 has been named "games". so here is the problem I need totals for points won (green) and points lost (red) for each player A2:A22 .
scenario: when i put a w in BO beside the team name like Ari it should color the cells with ARI in the game table green and then put the amount in the point won Col. then as games are played user will put in the "w" and "l"'s and as the games are all completed should look like the updated that I have included

ok, all seems good as far as the scoring, but I put all teams as a loss and I have a few that did not CF take a look

Good Morning ACE_XL

Well all is great with the sheet with on exception... I have imported this weeks games all the scores in just the first game seem to be picking up points from the very last game. as u will notice in the attached sheet. If I do not put the first game scores in all seems to be ok ?? I was wondering if it is the formula that pulls just the numbers out in U2 that is messing with it.

Seems that Stl,SD,Sea are somehow involved in the error? I tried the following: made another hidden sheet the brought the just the value() in the points field, made sure the names were in text form even tried capital L and W. there is a trigger here somewhere because if u mark all wins or all loss the correct totals are there? i did create names for the "teams" and the the "games" but didn't know if incorporating "names" in the formula would make any difference. This is just vary odd that the "s" teams would cause havoc like it is. Very good learning tool however. I have never used "sum product" before and have broke out my formula books to read up on its exact calculation procedure.
well there is some FYI for ya anyway

Your Great Thanks Such a little error caused so much HAVOC Thanks