Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum a set of cells based on comparing a different set of cells
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... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum a set of cells based on comparing a different set of cells
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 On May 15, 4:11*pm, BSweeney wrote: 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... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum a set of cells based on comparing a different set of cells
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 "BSweeney" wrote in message ... 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... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum a set of cells based on comparing a different set of cells
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! "Bernie Deitrick" wrote: 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 "BSweeney" wrote in message ... 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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
display a range of cells editible cells based on matching date | Excel Worksheet Functions | |||
Averaging Cells Based On Conditions in Neighboring Cells | Excel Discussion (Misc queries) | |||
conditional formating cells i Excel based on other cells values | Excel Worksheet Functions | |||
Conditional Formatting Multiple cells based on 2 cells | Excel Worksheet Functions | |||
Adding colour to a range of cells based on one of the cells v... | Excel Discussion (Misc queries) |