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!!

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!!

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!!

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!!

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!!

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!!

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!!