sum a set of cells based on comparing a different set of cells

B

BSweeney

I have a worksheet that has a set of values (numbers) in row G. I would like
to add those values but only if the values (which are letters) in row B match
letters that are in columns c through f. I will attempt to place an example
below this.


A B C D E F G H
1 TEAM A TA TB TC TD TE .5
2 TEAM B TB TC TD TB TE .2
3 TEAM C TC TD TB TF TG .1
4 TEAM D TD TE TG TH TB .7
5 TEAM E TE TF TB TC TH .3
6 TEAM F TF TG TA TB TD .3
7 TEAM G TG TH TC TD TA .4
8 TEAM H TH TA TD TE TF .6

So in this case, for Team A, they competed against Teams B, C, D and E. I
want to add the vales in Column G only for the teams that team A competed
against. I tried using the SumIf function, and write it something like this:

=sumif(c1:f1,b1:b8,g1:g8)

but that just returned a value of 0...

Is what I want to do possible? And if it is possible, how in the world
would I do this? Thanks in advance and I am sorry if this is too complicated
to read after I post it...
 
P

Pete_UK

Do you mean that in Team A's case, for example, they played B, C, D
and E, so their total comes from the points in the rows that those
teams are in? i.e. 0.2 + 0.1 + 0.7 + 0.3 (=1.3) ?

How can Team B play itself?

If Team A played B, C, D and E, then shouldn't we have TA on the rows
for each of those teams?

(I'm confused)

Pete
 
B

Bernie Deitrick

In H1, enter the formula

=VLOOKUP(C1,$B$1:$G:$8,6,False)

and copy to the right to cells I1:K1, then in L1, use =SUM(H1:K1)

Then copy H1:L1 down to H2:L8

HTH,
Bernie
MS Excel MVP
 
B

BSweeney

that worked perfectly. Thank you very much, sometimes the simple answers are
all thats needed, I was attempting to over complicate the function. Thanks
again!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top