Sumproduct Formula Help

  • Thread starter Thread starter Mark
  • Start date Start date
M

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
 
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
 
=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)
 
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)
 
I need to keep them in the order of the games played. So they are shown with
the "@" symbol. Any suggestions?
 
Back
Top