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

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



"Mark" wrote:

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

=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)



"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 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

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)

--
Biff
Microsoft Excel MVP


"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 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

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


"CLR" wrote:

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



"Mark" wrote:

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