Home 
Search 
Today's Posts 
#1




Formula to sum nonadjacent cells
Excell 2003
I have a 3000+ line spreadsheet with totals in one column every 17th row. What formula can I use to sum these totals? Thanks Jack 
#2




Formula to sum nonadjacent cells
On Mar 27, 9:04*am, Jack_Feeman
wrote: Excell 2003 I have a 3000+ line spreadsheet with totals in one column every 17th row. What formula can I use to sum these totals? Thanks Jack If the rest of the rows in that column are empty, you can just sum Else, a =SUMIF( would work well if there was some way you could differentiate those rows with a sum ... for example, if the word "sum" was in a previous column you could use something like this: =SUMIF(D1:E15,"sum",E1:E15) HTH Chris 
#3




Formula to sum nonadjacent cells
Thanks for the really quick reply Chris.
I will try your suggestion. I may have badly worded my question, here is a better description: "I have a 3000+ row spreadsheet with totals in the same column every 17th row." Would this clarification change your suggestion? Thanks Jack "cht13er" wrote: On Mar 27, 9:04 am, Jack_Feeman wrote: Excell 2003 I have a 3000+ line spreadsheet with totals in one column every 17th row. What formula can I use to sum these totals? Thanks Jack If the rest of the rows in that column are empty, you can just sum Else, a =SUMIF( would work well if there was some way you could differentiate those rows with a sum ... for example, if the word "sum" was in a previous column you could use something like this: =SUMIF(D1:E15,"sum",E1:E15) HTH Chris 
#4




Formula to sum nonadjacent cells
On Mar 27, 9:26*am, Jack_Feeman
wrote: Thanks for the really quick reply Chris. I will try your suggestion. I may have badly worded my question, here is a better description: "I have a 3000+ row spreadsheet with totals in the same column every 17th row." Would this clarification change your suggestion? Thanks Jack "cht13er" wrote: On Mar 27, 9:04 am, Jack_Feeman wrote: Excell 2003 I have a 3000+ line spreadsheet with totals in one column every 17th row. What formula can I use to sum these totals? Thanks Jack If the rest of the rows in that column are empty, you can just sum Else, a =SUMIF( would work well if there was some way you could differentiate those rows with a sum ... for example, if the word "sum" was in a previous column you could use something like this: =SUMIF(D1:E15,"sum",E1:E15) HTH Chris Hide quoted text   Show quoted text  So it's like 1 1 1 1 1 1 1 1 1 1 1 16 1 1 1 1 1 1 1 1 1 1 1 1 1 16 ? If so, the sum of the entire column is twice that of just the "every 17 rows" ... Chris 
#5




Formula to sum nonadjacent cells
Jack
You may find that you get more responses and ideas in microsoft.public.excel.worksheet.functions, which has more of a focus on worksheet formulas/functions. That said... I think Chris was indicating that we still don't have enough information to ensure the best solution. Does the column that contains the totals in every 17th row have other numbers or content inbetween the totals (row 116) or are they all blank? If they are all blank (or blank and text), just sum the whole column. If there are other numbers in rows 116 in that same column, then one option is to find a text identifier in a different column that /only/ occurs in the same rows as your totals (such as the word "Total"). Chris was suggesting that you use a formula that checks another column for some unique word, and then only add up the information in your Totals column from the rows that contain that unique word. If the other cells in your totals column contain numbers /and/ there are no unique identifiers on the totals rows (in other columns), then there are probably several ways to do this I'd be inclined to use an array formula. You enter an array formula by entering the formula, then with the cursor inside the cell, press CtlShiftEnter. It is entered properly if the formula (when you click back on the cell) has curly brackets { } around it. Here is a working sample formula that you can use as a starting point. Create a new worksheet and put some numbers in Column C, maybe rows 4 through 25. Then put this formula in a cell in another column (doesn't matter where) and press CtlShiftEnter. It should give you a total of the numbers in cells 7,14,21, and so on (every 7 cells). =SUM(IF(MOD(ROW(C4:C10000),7)=0,C4:C10000,0)) In your actual data worksheet you will need to modify the 7 to be the correct number of rows, the column C to whatever column you need. Your end result might look more like: =SUM(IF(MOD(ROW(H4:H10000),17)=0,H4:H10000,0)) You may need to tell the formula what cell to start in, if the first total is not actually in row 17 (e.g. if you have extra rows at the top of your data, maybe containing information about the report or something). The following change tells it that your first total is actually on row 19, and every total after that is still 17 rows apart. =SUM(IF(MOD(ROW(H4:H10000)2,17)=0,H4:H10000,0)) Best of luck, Keith "Jack_Feeman" wrote in message ... Thanks for the really quick reply Chris. I will try your suggestion. I may have badly worded my question, here is a better description: "I have a 3000+ row spreadsheet with totals in the same column every 17th row." Would this clarification change your suggestion? Thanks Jack "cht13er" wrote: On Mar 27, 9:04 am, Jack_Feeman wrote: Excell 2003 I have a 3000+ line spreadsheet with totals in one column every 17th row. What formula can I use to sum these totals? Thanks Jack If the rest of the rows in that column are empty, you can just sum Else, a =SUMIF( would work well if there was some way you could differentiate those rows with a sum ... for example, if the word "sum" was in a previous column you could use something like this: =SUMIF(D1:E15,"sum",E1:E15) HTH Chris 
#6




Formula to sum nonadjacent cells
Heh, I like that solution even better 8)
Keith "cht13er" wrote in message ... On Mar 27, 9:26 am, Jack_Feeman wrote: Thanks for the really quick reply Chris. I will try your suggestion. I may have badly worded my question, here is a better description: "I have a 3000+ row spreadsheet with totals in the same column every 17th row." Would this clarification change your suggestion? Thanks Jack "cht13er" wrote: On Mar 27, 9:04 am, Jack_Feeman wrote: Excell 2003 I have a 3000+ line spreadsheet with totals in one column every 17th row. What formula can I use to sum these totals? Thanks Jack If the rest of the rows in that column are empty, you can just sum Else, a =SUMIF( would work well if there was some way you could differentiate those rows with a sum ... for example, if the word "sum" was in a previous column you could use something like this: =SUMIF(D1:E15,"sum",E1:E15) HTH Chris Hide quoted text   Show quoted text  So it's like 1 1 1 1 1 1 1 1 1 1 1 16 1 1 1 1 1 1 1 1 1 1 1 1 1 16 ? If so, the sum of the entire column is twice that of just the "every 17 rows" ... Chris 
#7




Formula to sum nonadjacent cells
Keith and Chris,
I really appreciate your quick and specific responses. I will try your suggestions based on your assumptions. I will let you know either way if it works or not. "Ker_01" wrote: Jack You may find that you get more responses and ideas in microsoft.public.excel.worksheet.functions, which has more of a focus on worksheet formulas/functions. That said... I think Chris was indicating that we still don't have enough information to ensure the best solution. Does the column that contains the totals in every 17th row have other numbers or content inbetween the totals (row 116) or are they all blank? If they are all blank (or blank and text), just sum the whole column. If there are other numbers in rows 116 in that same column, then one option is to find a text identifier in a different column that /only/ occurs in the same rows as your totals (such as the word "Total"). Chris was suggesting that you use a formula that checks another column for some unique word, and then only add up the information in your Totals column from the rows that contain that unique word. If the other cells in your totals column contain numbers /and/ there are no unique identifiers on the totals rows (in other columns), then there are probably several ways to do this I'd be inclined to use an array formula. You enter an array formula by entering the formula, then with the cursor inside the cell, press CtlShiftEnter. It is entered properly if the formula (when you click back on the cell) has curly brackets { } around it. Here is a working sample formula that you can use as a starting point. Create a new worksheet and put some numbers in Column C, maybe rows 4 through 25. Then put this formula in a cell in another column (doesn't matter where) and press CtlShiftEnter. It should give you a total of the numbers in cells 7,14,21, and so on (every 7 cells). =SUM(IF(MOD(ROW(C4:C10000),7)=0,C4:C10000,0)) In your actual data worksheet you will need to modify the 7 to be the correct number of rows, the column C to whatever column you need. Your end result might look more like: =SUM(IF(MOD(ROW(H4:H10000),17)=0,H4:H10000,0)) You may need to tell the formula what cell to start in, if the first total is not actually in row 17 (e.g. if you have extra rows at the top of your data, maybe containing information about the report or something). The following change tells it that your first total is actually on row 19, and every total after that is still 17 rows apart. =SUM(IF(MOD(ROW(H4:H10000)2,17)=0,H4:H10000,0)) Best of luck, Keith "Jack_Feeman" wrote in message ... Thanks for the really quick reply Chris. I will try your suggestion. I may have badly worded my question, here is a better description: "I have a 3000+ row spreadsheet with totals in the same column every 17th row." Would this clarification change your suggestion? Thanks Jack "cht13er" wrote: On Mar 27, 9:04 am, Jack_Feeman wrote: Excell 2003 I have a 3000+ line spreadsheet with totals in one column every 17th row. What formula can I use to sum these totals? Thanks Jack If the rest of the rows in that column are empty, you can just sum Else, a =SUMIF( would work well if there was some way you could differentiate those rows with a sum ... for example, if the word "sum" was in a previous column you could use something like this: =SUMIF(D1:E15,"sum",E1:E15) HTH Chris 
#8




Formula to sum nonadjacent cells
Hi Jack,
Supose you data is in column A, you could add in column B this formula : =MOD(ROW(A1),17) Fill down And sum whre the result = 0 =SUMIF(B:B,0,A:A) HTH Regards JeanYves "Jack_Feeman" wrote in message ... Excell 2003 I have a 3000+ line spreadsheet with totals in one column every 17th row. What formula can I use to sum these totals? Thanks Jack 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Formula omits adjacent cells  Excel Discussion (Misc queries)  
Formula Omits Adjacent Cells  Excel Worksheet Functions  
copying adjacent cells with formula  Excel Worksheet Functions  
copying formula into nonadjacent cells, EXCEL2003  Excel Discussion (Misc queries)  
How can I add nonadjacent cells with a formula  Excel Worksheet Functions 