Sumproduct Formula Help

[Mark]

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 scores together. In The Below example, I need to add row 1 and 3
but I am having trouble using the sumproduct formula ignoring where they is
giving me problems--- Please Help!! How can I write this ignoring the (@ XXX).

A B
1 Knicks (@ Boston) 89
2 Boston (@ LA) 91
3 Knicks (@ Toronto) 105
4 Miami (@ LA) 112
5 Boston (@ Spurs) 107

 

[CLR]

Personally, I would use Data > TextToColumns to separate the teams out into
their own columns....then a regular SUMIF would work......and you could use
another SUMIF to figure their "away" points as well.........

Vaya con Dios,
Chuck, CABGx3

 

[Bob Phillips]

=SUM(IF(IF(ISNUMBER(FIND("(",A1:A10)),LEFT(A1:A10,FIND("(",A1:A10)-2),A1:A10)="Knicks",B1:B10))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[T. Valko]

Assuming you only want the total when it's the home team. For example, if an
entry was Miami (@ Knicks) you would not want that included in the total.

D1 = Knicks

=SUMPRODUCT(--(LEFT(A$1:A$5,LEN(D1))=D1),B$1:B$5)

 

[Mark]

I need to keep them in the order of the games played. So they are shown with
the "@" symbol. Any suggestions?