Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation Sum
Good afternoon,
Continuing with my spreadsheet (yay). I decided to modify it to SUM a row based upon a value entered in another field. Here is the (general) scenario. I have a group of people, who are either Team 1 or Team 2. Since they are setup alphabetically across the spreadsheet, I do not have the Teams grouped together. In a separate cell next to each individual's name, I have a field (VC1 or VC2, respectively). I am not sure if I explained myself clearly, but any assistance would be appreciated. Thank you. I want to be able to SUM all of the VC1s and VC2s, example: If D2 = VC1, then include B5 in the addition, if H2 = VC1, then include F5, etc. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation Sum
You'll have to better explain/describe you setup.
-- Biff Microsoft Excel MVP "Stacy C" wrote in message ... Good afternoon, Continuing with my spreadsheet (yay). I decided to modify it to SUM a row based upon a value entered in another field. Here is the (general) scenario. I have a group of people, who are either Team 1 or Team 2. Since they are setup alphabetically across the spreadsheet, I do not have the Teams grouped together. In a separate cell next to each individual's name, I have a field (VC1 or VC2, respectively). I am not sure if I explained myself clearly, but any assistance would be appreciated. Thank you. I want to be able to SUM all of the VC1s and VC2s, example: If D2 = VC1, then include B5 in the addition, if H2 = VC1, then include F5, etc. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation Sum
Ok, sorry :)
The cell which defines which team each person is on is every fourth cell beginning with D2, through EZ2 (each cell is either populated VC1 or VC2). I would like to have the cell FO5 sum the row across, using this formula: =SUMPRODUCT(--(MOD(COLUMN(C5:EZ5)-COLUMN(C5),4)=0),C5:EZ5) However, I only want to SUM the cells for persons who are in VC1 (i.e. - the 1st team). If you need further clarification, please let me know. Thank you so much. "T. Valko" wrote: You'll have to better explain/describe you setup. -- Biff Microsoft Excel MVP "Stacy C" wrote in message ... Good afternoon, Continuing with my spreadsheet (yay). I decided to modify it to SUM a row based upon a value entered in another field. Here is the (general) scenario. I have a group of people, who are either Team 1 or Team 2. Since they are setup alphabetically across the spreadsheet, I do not have the Teams grouped together. In a separate cell next to each individual's name, I have a field (VC1 or VC2, respectively). I am not sure if I explained myself clearly, but any assistance would be appreciated. Thank you. I want to be able to SUM all of the VC1s and VC2s, example: If D2 = VC1, then include B5 in the addition, if H2 = VC1, then include F5, etc. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation Sum
If I understand your setup, try this:
=SUMIF(D2:EZ2,"VC1",C5:EY5) =SUMIF(D2:EZ2,"VC2",C5:EY5) -- Biff Microsoft Excel MVP "Stacy C" wrote in message ... Ok, sorry :) The cell which defines which team each person is on is every fourth cell beginning with D2, through EZ2 (each cell is either populated VC1 or VC2). I would like to have the cell FO5 sum the row across, using this formula: =SUMPRODUCT(--(MOD(COLUMN(C5:EZ5)-COLUMN(C5),4)=0),C5:EZ5) However, I only want to SUM the cells for persons who are in VC1 (i.e. - the 1st team). If you need further clarification, please let me know. Thank you so much. "T. Valko" wrote: You'll have to better explain/describe you setup. -- Biff Microsoft Excel MVP "Stacy C" wrote in message ... Good afternoon, Continuing with my spreadsheet (yay). I decided to modify it to SUM a row based upon a value entered in another field. Here is the (general) scenario. I have a group of people, who are either Team 1 or Team 2. Since they are setup alphabetically across the spreadsheet, I do not have the Teams grouped together. In a separate cell next to each individual's name, I have a field (VC1 or VC2, respectively). I am not sure if I explained myself clearly, but any assistance would be appreciated. Thank you. I want to be able to SUM all of the VC1s and VC2s, example: If D2 = VC1, then include B5 in the addition, if H2 = VC1, then include F5, etc. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation Sum
I do not think this will work, only because I need it to add every 4th cell
across the row, if d2:ez2,"vc(x)". My sheet is setup like this: [ (text) ][ VC(x)][ (text) ][ VC(x) ] [Value1][Value2][Value3][Value4][Value1][Value2][Value3][Value4] (I apologize for the horrible "pictogram", but short of emailing the file I am at a loss for the explanation) so: The first part makes sense, the SUMIF(D2:EZ2,"VC1"...) However, I only need to add every 4th cell: I tried combining the formula I linked below but it didn't work, so I am sure that I am doing something wrong (which wouldn't surprise me - lol). I thank you for your kind assistance up til now, and any future advice will be appreciated. "T. Valko" wrote: If I understand your setup, try this: =SUMIF(D2:EZ2,"VC1",C5:EY5) =SUMIF(D2:EZ2,"VC2",C5:EY5) -- Biff Microsoft Excel MVP "Stacy C" wrote in message ... Ok, sorry :) The cell which defines which team each person is on is every fourth cell beginning with D2, through EZ2 (each cell is either populated VC1 or VC2). I would like to have the cell FO5 sum the row across, using this formula: =SUMPRODUCT(--(MOD(COLUMN(C5:EZ5)-COLUMN(C5),4)=0),C5:EZ5) However, I only want to SUM the cells for persons who are in VC1 (i.e. - the 1st team). If you need further clarification, please let me know. Thank you so much. "T. Valko" wrote: You'll have to better explain/describe you setup. -- Biff Microsoft Excel MVP "Stacy C" wrote in message ... Good afternoon, Continuing with my spreadsheet (yay). I decided to modify it to SUM a row based upon a value entered in another field. Here is the (general) scenario. I have a group of people, who are either Team 1 or Team 2. Since they are setup alphabetically across the spreadsheet, I do not have the Teams grouped together. In a separate cell next to each individual's name, I have a field (VC1 or VC2, respectively). I am not sure if I explained myself clearly, but any assistance would be appreciated. Thank you. I want to be able to SUM all of the VC1s and VC2s, example: If D2 = VC1, then include B5 in the addition, if H2 = VC1, then include F5, etc. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation Sum
Did you actually try the formula I suggested?
The range you want to sum if offset one column to the left of the range that contains the team code so that's why there's an offset in ranges in the formula. Try this experiemnt... In an empty sheet enter these values: D2 = VC1 H2 = VC2 C5:G5 = 1,2,3,4,5 Enter these formulas: A1: =SUMIF(D2:H2,"VC1",C5:G5) A2: =SUMIF(D2:H2,"VC2",C5:G5) The results will be 1 and 5 which are correct. -- Biff Microsoft Excel MVP "Stacy C" wrote in message ... I do not think this will work, only because I need it to add every 4th cell across the row, if d2:ez2,"vc(x)". My sheet is setup like this: [ (text) ][ VC(x)][ (text) ][ VC(x) ] [Value1][Value2][Value3][Value4][Value1][Value2][Value3][Value4] (I apologize for the horrible "pictogram", but short of emailing the file I am at a loss for the explanation) so: The first part makes sense, the SUMIF(D2:EZ2,"VC1"...) However, I only need to add every 4th cell: I tried combining the formula I linked below but it didn't work, so I am sure that I am doing something wrong (which wouldn't surprise me - lol). I thank you for your kind assistance up til now, and any future advice will be appreciated. "T. Valko" wrote: If I understand your setup, try this: =SUMIF(D2:EZ2,"VC1",C5:EY5) =SUMIF(D2:EZ2,"VC2",C5:EY5) -- Biff Microsoft Excel MVP "Stacy C" wrote in message ... Ok, sorry :) The cell which defines which team each person is on is every fourth cell beginning with D2, through EZ2 (each cell is either populated VC1 or VC2). I would like to have the cell FO5 sum the row across, using this formula: =SUMPRODUCT(--(MOD(COLUMN(C5:EZ5)-COLUMN(C5),4)=0),C5:EZ5) However, I only want to SUM the cells for persons who are in VC1 (i.e. - the 1st team). If you need further clarification, please let me know. Thank you so much. "T. Valko" wrote: You'll have to better explain/describe you setup. -- Biff Microsoft Excel MVP "Stacy C" wrote in message ... Good afternoon, Continuing with my spreadsheet (yay). I decided to modify it to SUM a row based upon a value entered in another field. Here is the (general) scenario. I have a group of people, who are either Team 1 or Team 2. Since they are setup alphabetically across the spreadsheet, I do not have the Teams grouped together. In a separate cell next to each individual's name, I have a field (VC1 or VC2, respectively). I am not sure if I explained myself clearly, but any assistance would be appreciated. Thank you. I want to be able to SUM all of the VC1s and VC2s, example: If D2 = VC1, then include B5 in the addition, if H2 = VC1, then include F5, etc. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation Sum
That works just fine, but I also want to be able to SUM the other cells. Each
column represents a different criteria used to derive a total. Consequently, I need to be able to derive each column independently. Maybe I need to further define the criteria? If it equals "VC1" and also "Sales", then C5:EZ5? "T. Valko" wrote: Did you actually try the formula I suggested? The range you want to sum if offset one column to the left of the range that contains the team code so that's why there's an offset in ranges in the formula. Try this experiemnt... In an empty sheet enter these values: D2 = VC1 H2 = VC2 C5:G5 = 1,2,3,4,5 Enter these formulas: A1: =SUMIF(D2:H2,"VC1",C5:G5) A2: =SUMIF(D2:H2,"VC2",C5:G5) The results will be 1 and 5 which are correct. -- Biff Microsoft Excel MVP "Stacy C" wrote in message ... I do not think this will work, only because I need it to add every 4th cell across the row, if d2:ez2,"vc(x)". My sheet is setup like this: [ (text) ][ VC(x)][ (text) ][ VC(x) ] [Value1][Value2][Value3][Value4][Value1][Value2][Value3][Value4] (I apologize for the horrible "pictogram", but short of emailing the file I am at a loss for the explanation) so: The first part makes sense, the SUMIF(D2:EZ2,"VC1"...) However, I only need to add every 4th cell: I tried combining the formula I linked below but it didn't work, so I am sure that I am doing something wrong (which wouldn't surprise me - lol). I thank you for your kind assistance up til now, and any future advice will be appreciated. "T. Valko" wrote: If I understand your setup, try this: =SUMIF(D2:EZ2,"VC1",C5:EY5) =SUMIF(D2:EZ2,"VC2",C5:EY5) -- Biff Microsoft Excel MVP "Stacy C" wrote in message ... Ok, sorry :) The cell which defines which team each person is on is every fourth cell beginning with D2, through EZ2 (each cell is either populated VC1 or VC2). I would like to have the cell FO5 sum the row across, using this formula: =SUMPRODUCT(--(MOD(COLUMN(C5:EZ5)-COLUMN(C5),4)=0),C5:EZ5) However, I only want to SUM the cells for persons who are in VC1 (i.e. - the 1st team). If you need further clarification, please let me know. Thank you so much. "T. Valko" wrote: You'll have to better explain/describe you setup. -- Biff Microsoft Excel MVP "Stacy C" wrote in message ... Good afternoon, Continuing with my spreadsheet (yay). I decided to modify it to SUM a row based upon a value entered in another field. Here is the (general) scenario. I have a group of people, who are either Team 1 or Team 2. Since they are setup alphabetically across the spreadsheet, I do not have the Teams grouped together. In a separate cell next to each individual's name, I have a field (VC1 or VC2, respectively). I am not sure if I explained myself clearly, but any assistance would be appreciated. Thank you. I want to be able to SUM all of the VC1s and VC2s, example: If D2 = VC1, then include B5 in the addition, if H2 = VC1, then include F5, etc. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation Sum
Ok, now I'm totally lost!
Not much I can suggest without seeing this in front me. If your file isn't too big (but it sounds like it is) you can upload a copy to a free file host. Then you can post a link to the file so that anyone so inclined can download the file and see what it is you're trying to do. -- Biff Microsoft Excel MVP "Stacy C" wrote in message ... That works just fine, but I also want to be able to SUM the other cells. Each column represents a different criteria used to derive a total. Consequently, I need to be able to derive each column independently. Maybe I need to further define the criteria? If it equals "VC1" and also "Sales", then C5:EZ5? "T. Valko" wrote: Did you actually try the formula I suggested? The range you want to sum if offset one column to the left of the range that contains the team code so that's why there's an offset in ranges in the formula. Try this experiemnt... In an empty sheet enter these values: D2 = VC1 H2 = VC2 C5:G5 = 1,2,3,4,5 Enter these formulas: A1: =SUMIF(D2:H2,"VC1",C5:G5) A2: =SUMIF(D2:H2,"VC2",C5:G5) The results will be 1 and 5 which are correct. -- Biff Microsoft Excel MVP "Stacy C" wrote in message ... I do not think this will work, only because I need it to add every 4th cell across the row, if d2:ez2,"vc(x)". My sheet is setup like this: [ (text) ][ VC(x)][ (text) ][ VC(x) ] [Value1][Value2][Value3][Value4][Value1][Value2][Value3][Value4] (I apologize for the horrible "pictogram", but short of emailing the file I am at a loss for the explanation) so: The first part makes sense, the SUMIF(D2:EZ2,"VC1"...) However, I only need to add every 4th cell: I tried combining the formula I linked below but it didn't work, so I am sure that I am doing something wrong (which wouldn't surprise me - lol). I thank you for your kind assistance up til now, and any future advice will be appreciated. "T. Valko" wrote: If I understand your setup, try this: =SUMIF(D2:EZ2,"VC1",C5:EY5) =SUMIF(D2:EZ2,"VC2",C5:EY5) -- Biff Microsoft Excel MVP "Stacy C" wrote in message ... Ok, sorry :) The cell which defines which team each person is on is every fourth cell beginning with D2, through EZ2 (each cell is either populated VC1 or VC2). I would like to have the cell FO5 sum the row across, using this formula: =SUMPRODUCT(--(MOD(COLUMN(C5:EZ5)-COLUMN(C5),4)=0),C5:EZ5) However, I only want to SUM the cells for persons who are in VC1 (i.e. - the 1st team). If you need further clarification, please let me know. Thank you so much. "T. Valko" wrote: You'll have to better explain/describe you setup. -- Biff Microsoft Excel MVP "Stacy C" wrote in message ... Good afternoon, Continuing with my spreadsheet (yay). I decided to modify it to SUM a row based upon a value entered in another field. Here is the (general) scenario. I have a group of people, who are either Team 1 or Team 2. Since they are setup alphabetically across the spreadsheet, I do not have the Teams grouped together. In a separate cell next to each individual's name, I have a field (VC1 or VC2, respectively). I am not sure if I explained myself clearly, but any assistance would be appreciated. Thank you. I want to be able to SUM all of the VC1s and VC2s, example: If D2 = VC1, then include B5 in the addition, if H2 = VC1, then include F5, etc. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation Data using Validation Table cell range..... | Excel Discussion (Misc queries) | |||
Data Validation Update Validation Selection | Excel Worksheet Functions | |||
data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
Data validation with validation lists and combo boxs | Excel Discussion (Misc queries) |