lookup and sum.. or count?

**Taylor_1978** · 05-31-2007, 09:50 PM

Hi,

I am not sure if I am on the right track or not and my thread title may be misleading! But didn't want to just post, "HELP" - but help is exactly what I need.

You will see in the screen shot I have included in this post I have a list of teams and next to their name how many games they have won ("W") - Columns AG & AH.

In columns Z & AA there are a list of teams again in different orders and next to them is a letter, "W" for won, "L" for lost, "D" for drawn and "B" for Bye.

I want the Column AH to add how many times that specific team has a W next to their name in columns Z & AA. So for example in the screen shot I have provided, the team "Colin #2" should have 1 under W in column AH.

I have tried using a combination of sum, count, lookup and if - but just don't seem to be getting anywhere.

Any help appreciated. Here is a link to the screen shot:

http://citysidesports.com/excel.jpg

EDIT: I should also mention that a team can be listed up to 10 times in no certain order in column AA.

Thanks!

**rylo** · 05-31-2007, 09:57 PM

HI

AH10: =sumproduct(--($aa$20:$aa$29=AG10),--($Z$20:$Z$29="W"))

Adjust the ranges to suit and copy down. I'm guessing that the merged cells in AA:AD have the data taken from AA.

rylo

**Taylor_1978** · 05-31-2007, 10:02 PM

Worked a charm! Thank you very much rylo!

**Taylor_1978** · 05-31-2007, 10:05 PM

One other problem! lol

Next to the W or L in Colomn Z is Column Y which has the teams score. So I'm also wanting to add up the scores of each game. Did that make sense?

Thanks.

**rylo** · 05-31-2007, 10:15 PM

Hi

Expand it to include column Y

AH10: =sumproduct(--($aa$20:$aa$29=AG10),--($Z$20:$Z$29="W"),($y$10:$y$29))

rylo

**Taylor_1978** · 05-31-2007, 10:26 PM

That one didn't work (#!VALUE) but I probably didn't explain myself properly.

In Column AL, which is labeled "Score For" it is supposed to tally up all the scores of the team, regardless of whether it's a win, loss or draw.

Column Y shows the teams score.

I have included another screen shot to explain what I mean:

http://citysidesports.com/excel2.jpg

If you look at the team "The Comrades #2" it lists they have 2 wins no problem. So that works. Under the column "For" it should have 27 being 15 + 12 from their 2 games played.

I hope that is explained better!