| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula needed for adding every nth column on another sheet
Hi
I have tried everything I can think of but with no luck - can somebody please help. On sheet 2 I have all the data in columns from rows 1-20. On sheet 1 I want the total of the sums of every 3rd column. ie. Sheet 1: A1 = sum Sheet 2: A1:A20 Sheet 1: B1 = sum Sheet 2: D1:D20 Sheet 1: C1 = sum Sheet 2: G1:G20 and so on... Instead of manually entering all the formulas on Sheet 1 for the sum totals of every 3rd column, is there a formula I can enter in A1 on Sheet 1 and copy across the row? Thanks for any help!! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula needed for adding every nth column on another sheet
Try this:
=SUM(INDEX(Sheet2!$A1:$AB20,1,COLUMNS($A:A)*3-2):INDEX(Sheet2!$A1:$AB20,20,COLUMNS($A:A)*3-2)) You can enter this *anywhere*, and copy across as needed, though I've only sized this out to Column AB! -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "JJ" wrote in message ... Hi I have tried everything I can think of but with no luck - can somebody please help. On sheet 2 I have all the data in columns from rows 1-20. On sheet 1 I want the total of the sums of every 3rd column. ie. Sheet 1: A1 = sum Sheet 2: A1:A20 Sheet 1: B1 = sum Sheet 2: D1:D20 Sheet 1: C1 = sum Sheet 2: G1:G20 and so on... Instead of manually entering all the formulas on Sheet 1 for the sum totals of every 3rd column, is there a formula I can enter in A1 on Sheet 1 and copy across the row? Thanks for any help!! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula needed for adding every nth column on another sheet
Thanks RagDyer, it worked a treat!! I would never have got that myself.
"RagDyer" wrote: Try this: =SUM(INDEX(Sheet2!$A1:$AB20,1,COLUMNS($A:A)*3-2):INDEX(Sheet2!$A1:$AB20,20,COLUMNS($A:A)*3-2)) You can enter this *anywhere*, and copy across as needed, though I've only sized this out to Column AB! -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "JJ" wrote in message ... Hi I have tried everything I can think of but with no luck - can somebody please help. On sheet 2 I have all the data in columns from rows 1-20. On sheet 1 I want the total of the sums of every 3rd column. ie. Sheet 1: A1 = sum Sheet 2: A1:A20 Sheet 1: B1 = sum Sheet 2: D1:D20 Sheet 1: C1 = sum Sheet 2: G1:G20 and so on... Instead of manually entering all the formulas on Sheet 1 for the sum totals of every 3rd column, is there a formula I can enter in A1 on Sheet 1 and copy across the row? Thanks for any help!! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula needed for adding every nth column on another sheet
Shorter version:
=SUM(INDEX(Sheet2!$1:$20,,COLUMNS($A:A)*3-2)) "RagDyer" wrote: Try this: =SUM(INDEX(Sheet2!$A1:$AB20,1,COLUMNS($A:A)*3-2):INDEX(Sheet2!$A1:$AB20,20,COLUMNS($A:A)*3-2)) You can enter this *anywhere*, and copy across as needed, though I've only sized this out to Column AB! -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "JJ" wrote in message ... Hi I have tried everything I can think of but with no luck - can somebody please help. On sheet 2 I have all the data in columns from rows 1-20. On sheet 1 I want the total of the sums of every 3rd column. ie. Sheet 1: A1 = sum Sheet 2: A1:A20 Sheet 1: B1 = sum Sheet 2: D1:D20 Sheet 1: C1 = sum Sheet 2: G1:G20 and so on... Instead of manually entering all the formulas on Sheet 1 for the sum totals of every 3rd column, is there a formula I can enter in A1 on Sheet 1 and copy across the row? Thanks for any help!! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula needed for adding every nth column on another sheet
Nice!
I've noticed that you DO have a fine comprehension of that Index() function!<g -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Teethless mama" wrote in message ... Shorter version: =SUM(INDEX(Sheet2!$1:$20,,COLUMNS($A:A)*3-2)) "RagDyer" wrote: Try this: =SUM(INDEX(Sheet2!$A1:$AB20,1,COLUMNS($A:A)*3-2):INDEX(Sheet2!$A1:$AB20,20,COLUMNS($A:A)*3-2)) You can enter this *anywhere*, and copy across as needed, though I've only sized this out to Column AB! -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "JJ" wrote in message ... Hi I have tried everything I can think of but with no luck - can somebody please help. On sheet 2 I have all the data in columns from rows 1-20. On sheet 1 I want the total of the sums of every 3rd column. ie. Sheet 1: A1 = sum Sheet 2: A1:A20 Sheet 1: B1 = sum Sheet 2: D1:D20 Sheet 1: C1 = sum Sheet 2: G1:G20 and so on... Instead of manually entering all the formulas on Sheet 1 for the sum totals of every 3rd column, is there a formula I can enter in A1 on Sheet 1 and copy across the row? Thanks for any help!! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula needed for adding every nth column on another sheet
You're welcome, and appreciate the feed-back, but do check out Tm's shorter
formula. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "JJ" wrote in message ... Thanks RagDyer, it worked a treat!! I would never have got that myself. "RagDyer" wrote: Try this: =SUM(INDEX(Sheet2!$A1:$AB20,1,COLUMNS($A:A)*3-2):INDEX(Sheet2!$A1:$AB20,20,COLUMNS($A:A)*3-2)) You can enter this *anywhere*, and copy across as needed, though I've only sized this out to Column AB! -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "JJ" wrote in message ... Hi I have tried everything I can think of but with no luck - can somebody please help. On sheet 2 I have all the data in columns from rows 1-20. On sheet 1 I want the total of the sums of every 3rd column. ie. Sheet 1: A1 = sum Sheet 2: A1:A20 Sheet 1: B1 = sum Sheet 2: D1:D20 Sheet 1: C1 = sum Sheet 2: G1:G20 and so on... Instead of manually entering all the formulas on Sheet 1 for the sum totals of every 3rd column, is there a formula I can enter in A1 on Sheet 1 and copy across the row? Thanks for any help!! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula needed for adding every nth column on another sheet
Sheet1!A1:
=SUM(OFFSET(Sheet2!$A$1,0,(COLUMN()-1)*3,20,1)) and fill right Bob Umlas Excel MVP "JJ" wrote in message ... Hi I have tried everything I can think of but with no luck - can somebody please help. On sheet 2 I have all the data in columns from rows 1-20. On sheet 1 I want the total of the sums of every 3rd column. ie. Sheet 1: A1 = sum Sheet 2: A1:A20 Sheet 1: B1 = sum Sheet 2: D1:D20 Sheet 1: C1 = sum Sheet 2: G1:G20 and so on... Instead of manually entering all the formulas on Sheet 1 for the sum totals of every 3rd column, is there a formula I can enter in A1 on Sheet 1 and copy across the row? Thanks for any help!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sheet name needed for formula | Excel Worksheet Functions | |||
| formula needed to bring values to another sheet | Excel Worksheet Functions | |||
| Adding a Comment-Column to an external data-sheet | Excel Worksheet Functions | |||
| Compare one Column to a Second Column Formula Help Needed | Excel Worksheet Functions | |||
| Adding Column in an Excel Sheet by Command | Excel Worksheet Functions | |||
Linear Mode
