I am trying to write a program to collect scores of basketbal games. Here is
what I have. Column A is the team and where they played (varies). Column B is
the score. I would like to look-up the team (ignoring where they played) and
add all the score together. In The Below example, I need to add row 1 and 3
but I am having trouble using the sumproduct formula ignoring the where thay
played. Help!!

A B
1 TEAM A (@ XXX) SCORE
2 TEAM B (@ XXX) SCORE
3 TEAM A (@ YYY) SCORE
4 TEAM C (@ ZZZ) SCORE
5 TEAM B (@ VVV) SCORE

Mark,

Is there any way to put the location in another column?

If not I think I have something that will work.

This will probably work best if you have a list of the teams below your data
and you are trying to sum all of the scores for all of your teams.

Using your example:
--In cells A7:A9 have the teams (TEAM A, TEAM B, TEAM C)
(i'm guessing that those are not the actual team names, but my solution will
work with different names and even different length names)
--In cell B7 enter this formula:
=SUMPRODUCT((LEFT($A$2:$A$6,LEN($A7))=$A7)*($B$2:$ B$6))
(you should be able to copy-n-paste...tested this formula and it appears to
work the way you want it to)
--Copy formula down to cells B8 & B9

HTH,

Conan






"Mark" wrote in message
...
I am trying to write a program to collect scores of basketbal games. Here
is
what I have. Column A is the team and where they played (varies). Column B
is
the score. I would like to look-up the team (ignoring where they played)
and
add all the score together. In The Below example, I need to add row 1 and
3
but I am having trouble using the sumproduct formula ignoring the where
thay
played. Help!!

A B
1 TEAM A (@ XXX) SCORE
2 TEAM B (@ XXX) SCORE
3 TEAM A (@ YYY) SCORE
4 TEAM C (@ ZZZ) SCORE
5 TEAM B (@ VVV) SCORE