I am converting a monthly revenue table to a quarterly revenue table. I would
like to add A1:C1 and put it the result in a new cell and then autofill the
cell to the right of it so that it automatically adds D1:F1 for the next value
A B C D E F
1 Jan Feb March April May June
2 10 12 12 13 14 15

Qtr1 Qtr 2 Qtr 3
sum A2:C2 Sum D2:F2 Etc... using autofill
currently using autofill the first qtr is correct "=Sum(A2:C2) but the next
cell gets filled with "=sum(B2:D2) instead of adding the next 3 months.

I appreciate anyone's help!

One way:

Assume the first formula is entered in A4:

=SUM(OFFSET($A1,,(COLUMNS($A4:A4)-1)*3,,3))

However, if this is for just a single year which only has 4 quarters, what's
wrong with using 4 simple sum formulas:

=SUM(A1:C1)
=SUM(D1:F1)
=SUM(G1:I1)
=SUM(J1:L1)

Using those 4 formulas is better than using the formula I suggested.


--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
I am converting a monthly revenue table to a quarterly revenue table. I
would
like to add A1:C1 and put it the result in a new cell and then autofill
the
cell to the right of it so that it automatically adds D1:F1 for the next
value
A B C D E F
1 Jan Feb March April May June
2 10 12 12 13 14 15

Qtr1 Qtr 2 Qtr 3
sum A2:C2 Sum D2:F2 Etc... using autofill
currently using autofill the first qtr is correct "=Sum(A2:C2) but the
next
cell gets filled with "=sum(B2:D2) instead of adding the next 3 months.

I appreciate anyone's help!

Put first formula =SUM(A2:C2) in whatever cell you wanted, lets assume A5.
Put next formula =SUM(D2:F2) in cell 3 cells to the right, eg D5. Now
select A5:F5. Now grab the fill handle and fill right as far as necessary
(lets assume IV5).
When done simply select A5:IV5, do Edit / Go To / Special / Blank cells,
then do edit / delete / shift cells to left.

Job done

Regards
Ken.........................



"Steve" wrote in message
...
I am converting a monthly revenue table to a quarterly revenue table. I
would
like to add A1:C1 and put it the result in a new cell and then autofill
the
cell to the right of it so that it automatically adds D1:F1 for the next
value
A B C D E F
1 Jan Feb March April May June
2 10 12 12 13 14 15

Qtr1 Qtr 2 Qtr 3
sum A2:C2 Sum D2:F2 Etc... using autofill
currently using autofill the first qtr is correct "=Sum(A2:C2) but the
next
cell gets filled with "=sum(B2:D2) instead of adding the next 3 months.

I appreciate anyone's help!

Hi T. Valko,

The formula worked so thank you! :)

I now understand this sum offset function with the exception of this column
statement:
(COLUMNS($A4:A4)-1)*3
Can you explain how this works?
Specific questions about how this works:
What is the purpose for the column array getting wider as I autofill the
cells to the right with this formula? (Next cell has $A4:B4 then the next
cell has $A4:C4, etc...)
What is the purpose of "-1" in this expression
What is the purpose of "*3" in this expression
I know that overall this is expression identifies the 3 columns where the
values are to be summed, but I don't understand the logic.

In order for me to correctly write and use this formula in the future, I
need to be able to understand this column expression.

PS I used one year as an example but I have a lot more years per request and
then I have often have to do a CY quarterly version and a FY quarterly
version of the monthly tables, so I thought there had to be an easier way
worth learning. :)

"T. Valko" wrote:

One way:

Assume the first formula is entered in A4:

=SUM(OFFSET($A1,,(COLUMNS($A4:A4)-1)*3,,3))

However, if this is for just a single year which only has 4 quarters, what's
wrong with using 4 simple sum formulas:

=SUM(A1:C1)
=SUM(D1:F1)
=SUM(G1:I1)
=SUM(J1:L1)

Using those 4 formulas is better than using the formula I suggested.


--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
I am converting a monthly revenue table to a quarterly revenue table. I
would
like to add A1:C1 and put it the result in a new cell and then autofill
the
cell to the right of it so that it automatically adds D1:F1 for the next
value
A B C D E F
1 Jan Feb March April May June
2 10 12 12 13 14 15

Qtr1 Qtr 2 Qtr 3
sum A2:C2 Sum D2:F2 Etc... using autofill
currently using autofill the first qtr is correct "=Sum(A2:C2) but the
next
cell gets filled with "=sum(B2:D2) instead of adding the next 3 months.

I appreciate anyone's help!

Hi Ken,

This suggestion also worked. The use of the "edit command" for deleting
blank cells is something I have needed for many other situations so thank you.

Steve



"Ken Wright" wrote:

Put first formula =SUM(A2:C2) in whatever cell you wanted, lets assume A5.
Put next formula =SUM(D2:F2) in cell 3 cells to the right, eg D5. Now
select A5:F5. Now grab the fill handle and fill right as far as necessary
(lets assume IV5).
When done simply select A5:IV5, do Edit / Go To / Special / Blank cells,
then do edit / delete / shift cells to left.

Job done

Regards
Ken.........................



"Steve" wrote in message
...
I am converting a monthly revenue table to a quarterly revenue table. I
would
like to add A1:C1 and put it the result in a new cell and then autofill
the
cell to the right of it so that it automatically adds D1:F1 for the next
value
A B C D E F
1 Jan Feb March April May June
2 10 12 12 13 14 15

Qtr1 Qtr 2 Qtr 3
sum A2:C2 Sum D2:F2 Etc... using autofill
currently using autofill the first qtr is correct "=Sum(A2:C2) but the
next
cell gets filled with "=sum(B2:D2) instead of adding the next 3 months.

I appreciate anyone's help!

Here's how this works...

=SUM(OFFSET($A1,,(COLUMNS($A4:A4)-1)*3,,3))

You want to sum groups of 3 cells starting from cell A1.

We use the OFFSET function to pass the range of these 3 cells to the SUM
function.

These are the arguments that OFFSET takes:

OFFSET(reference,rows,cols,height,width)

$A1 is the reference or "anchor" cell. That's where we're starting from.

Since the data we're interested in is all on the same row we don't need to
offset A1 by any rows so that argument is empty and defaults to 0. That
means offset A1 by 0 rows.

We're interested in cells in groups of 3 so we need to offset A1 by 3
columns with each cell that we copy the formula to. That's what the cols
argument (COLUMNS($A4:A4)-1)*3 is doing.

The groups of cells we're interested in are A1:C1, D1:F1, G1:I1, J1:L1, etc.

So, we use (COLUMNS($A4:A4)-1)*3 to increment the column offset by 3 for
each cell that the formula is copied to.

With the formula entered in the first cell of A4, COLUMNS($A4:A4) evaluates
to 1. The columns function simply counts the number of columns referenced in
its argument. As we copy the formula across the range reference will
incremnt like this:

COLUMNS($A4:A4) = 1
COLUMNS($A4:B4) = 2
COLUMNS($A4:C4) = 3
COLUMNS($A4:D4) = 4

We use the multiplier of 3 to increment the offset by groups of 3.

So COLUMNS($A4:A4)*3 = 3 which means offset A1 by 3 columns and that would
put us at cell D1. However, we want to start at cell A1 not cell D1 so we
use -1 to adjust for this. Like this:

(COLUMNS($A4:A4)-1) = 0 * 3 = 0
(COLUMNS($A4:B4)-1) = 1 * 3 = 3
(COLUMNS($A4:C4)-1) = 2 * 3 = 6
(COLUMNS($A4:D4)-1) = 3 * 3 = 9

So, this means offset A1 by 0 columns, 3 columns, 6 columns, 9 columns, etc.

Now, we need to tell OFFSET how high and wide the range we're interested in
is. Since the data is on the same row we leave the height argument empty and
this defaults to 1. We want to sum every 3 cells so the width argument is 3.

So, in plain English:

A4 = offset A1 by 0 columns and sum A1:C1
B4 = offset A1 by 3 columns and sum D1:F1
C4 = offset A1 by 6 columns and sum G1:I1
D4 = offset A1 by 9 columns and sum J1:L1


--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
Hi T. Valko,

The formula worked so thank you! :)

I now understand this sum offset function with the exception of this
column
statement:
(COLUMNS($A4:A4)-1)*3
Can you explain how this works?
Specific questions about how this works:
What is the purpose for the column array getting wider as I autofill the
cells to the right with this formula? (Next cell has $A4:B4 then the next
cell has $A4:C4, etc...)
What is the purpose of "-1" in this expression
What is the purpose of "*3" in this expression
I know that overall this is expression identifies the 3 columns where the
values are to be summed, but I don't understand the logic.

In order for me to correctly write and use this formula in the future, I
need to be able to understand this column expression.

PS I used one year as an example but I have a lot more years per request
and
then I have often have to do a CY quarterly version and a FY quarterly
version of the monthly tables, so I thought there had to be an easier way
worth learning. :)

"T. Valko" wrote:

One way:

Assume the first formula is entered in A4:

=SUM(OFFSET($A1,,(COLUMNS($A4:A4)-1)*3,,3))

However, if this is for just a single year which only has 4 quarters,
what's
wrong with using 4 simple sum formulas:

=SUM(A1:C1)
=SUM(D1:F1)
=SUM(G1:I1)
=SUM(J1:L1)

Using those 4 formulas is better than using the formula I suggested.


--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
I am converting a monthly revenue table to a quarterly revenue table. I
would
like to add A1:C1 and put it the result in a new cell and then autofill
the
cell to the right of it so that it automatically adds D1:F1 for the
next
value
A B C D E F
1 Jan Feb March April May June
2 10 12 12 13 14 15

Qtr1 Qtr 2 Qtr 3
sum A2:C2 Sum D2:F2 Etc... using autofill
currently using autofill the first qtr is correct "=Sum(A2:C2) but the
next
cell gets filled with "=sum(B2:D2) instead of adding the next 3 months.

I appreciate anyone's help!

This was an absolutely perfect explanation of how it works. Thank you for
going "above and beyond" to explain the solution in a clear and concise
manner.

Steve


"T. Valko" wrote:

Here's how this works...

=SUM(OFFSET($A1,,(COLUMNS($A4:A4)-1)*3,,3))

You want to sum groups of 3 cells starting from cell A1.

We use the OFFSET function to pass the range of these 3 cells to the SUM
function.

These are the arguments that OFFSET takes:

OFFSET(reference,rows,cols,height,width)

$A1 is the reference or "anchor" cell. That's where we're starting from.

Since the data we're interested in is all on the same row we don't need to
offset A1 by any rows so that argument is empty and defaults to 0. That
means offset A1 by 0 rows.

We're interested in cells in groups of 3 so we need to offset A1 by 3
columns with each cell that we copy the formula to. That's what the cols
argument (COLUMNS($A4:A4)-1)*3 is doing.

The groups of cells we're interested in are A1:C1, D1:F1, G1:I1, J1:L1, etc.

So, we use (COLUMNS($A4:A4)-1)*3 to increment the column offset by 3 for
each cell that the formula is copied to.

With the formula entered in the first cell of A4, COLUMNS($A4:A4) evaluates
to 1. The columns function simply counts the number of columns referenced in
its argument. As we copy the formula across the range reference will
incremnt like this:

COLUMNS($A4:A4) = 1
COLUMNS($A4:B4) = 2
COLUMNS($A4:C4) = 3
COLUMNS($A4:D4) = 4

We use the multiplier of 3 to increment the offset by groups of 3.

So COLUMNS($A4:A4)*3 = 3 which means offset A1 by 3 columns and that would
put us at cell D1. However, we want to start at cell A1 not cell D1 so we
use -1 to adjust for this. Like this:

(COLUMNS($A4:A4)-1) = 0 * 3 = 0
(COLUMNS($A4:B4)-1) = 1 * 3 = 3
(COLUMNS($A4:C4)-1) = 2 * 3 = 6
(COLUMNS($A4:D4)-1) = 3 * 3 = 9

So, this means offset A1 by 0 columns, 3 columns, 6 columns, 9 columns, etc.

Now, we need to tell OFFSET how high and wide the range we're interested in
is. Since the data is on the same row we leave the height argument empty and
this defaults to 1. We want to sum every 3 cells so the width argument is 3.

So, in plain English:

A4 = offset A1 by 0 columns and sum A1:C1
B4 = offset A1 by 3 columns and sum D1:F1
C4 = offset A1 by 6 columns and sum G1:I1
D4 = offset A1 by 9 columns and sum J1:L1


--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
Hi T. Valko,

The formula worked so thank you! :)

I now understand this sum offset function with the exception of this
column
statement:
(COLUMNS($A4:A4)-1)*3
Can you explain how this works?
Specific questions about how this works:
What is the purpose for the column array getting wider as I autofill the
cells to the right with this formula? (Next cell has $A4:B4 then the next
cell has $A4:C4, etc...)
What is the purpose of "-1" in this expression
What is the purpose of "*3" in this expression
I know that overall this is expression identifies the 3 columns where the
values are to be summed, but I don't understand the logic.

In order for me to correctly write and use this formula in the future, I
need to be able to understand this column expression.

PS I used one year as an example but I have a lot more years per request
and
then I have often have to do a CY quarterly version and a FY quarterly
version of the monthly tables, so I thought there had to be an easier way
worth learning. :)

"T. Valko" wrote:

One way:

Assume the first formula is entered in A4:

=SUM(OFFSET($A1,,(COLUMNS($A4:A4)-1)*3,,3))

However, if this is for just a single year which only has 4 quarters,
what's
wrong with using 4 simple sum formulas:

=SUM(A1:C1)
=SUM(D1:F1)
=SUM(G1:I1)
=SUM(J1:L1)

Using those 4 formulas is better than using the formula I suggested.


--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
I am converting a monthly revenue table to a quarterly revenue table. I
would
like to add A1:C1 and put it the result in a new cell and then autofill
the
cell to the right of it so that it automatically adds D1:F1 for the
next
value
A B C D E F
1 Jan Feb March April May June
2 10 12 12 13 14 15

Qtr1 Qtr 2 Qtr 3
sum A2:C2 Sum D2:F2 Etc... using autofill
currently using autofill the first qtr is correct "=Sum(A2:C2) but the
next
cell gets filled with "=sum(B2:D2) instead of adding the next 3 months.

I appreciate anyone's help!

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
This was an absolutely perfect explanation of how it works. Thank you for
going "above and beyond" to explain the solution in a clear and concise
manner.

Steve


"T. Valko" wrote:

Here's how this works...

=SUM(OFFSET($A1,,(COLUMNS($A4:A4)-1)*3,,3))

You want to sum groups of 3 cells starting from cell A1.

We use the OFFSET function to pass the range of these 3 cells to the SUM
function.

These are the arguments that OFFSET takes:

OFFSET(reference,rows,cols,height,width)

$A1 is the reference or "anchor" cell. That's where we're starting from.

Since the data we're interested in is all on the same row we don't need
to
offset A1 by any rows so that argument is empty and defaults to 0. That
means offset A1 by 0 rows.

We're interested in cells in groups of 3 so we need to offset A1 by 3
columns with each cell that we copy the formula to. That's what the cols
argument (COLUMNS($A4:A4)-1)*3 is doing.

The groups of cells we're interested in are A1:C1, D1:F1, G1:I1, J1:L1,
etc.

So, we use (COLUMNS($A4:A4)-1)*3 to increment the column offset by 3 for
each cell that the formula is copied to.

With the formula entered in the first cell of A4, COLUMNS($A4:A4)
evaluates
to 1. The columns function simply counts the number of columns referenced
in
its argument. As we copy the formula across the range reference will
incremnt like this:

COLUMNS($A4:A4) = 1
COLUMNS($A4:B4) = 2
COLUMNS($A4:C4) = 3
COLUMNS($A4:D4) = 4

We use the multiplier of 3 to increment the offset by groups of 3.

So COLUMNS($A4:A4)*3 = 3 which means offset A1 by 3 columns and that
would
put us at cell D1. However, we want to start at cell A1 not cell D1 so we
use -1 to adjust for this. Like this:

(COLUMNS($A4:A4)-1) = 0 * 3 = 0
(COLUMNS($A4:B4)-1) = 1 * 3 = 3
(COLUMNS($A4:C4)-1) = 2 * 3 = 6
(COLUMNS($A4:D4)-1) = 3 * 3 = 9

So, this means offset A1 by 0 columns, 3 columns, 6 columns, 9 columns,
etc.

Now, we need to tell OFFSET how high and wide the range we're interested
in
is. Since the data is on the same row we leave the height argument empty
and
this defaults to 1. We want to sum every 3 cells so the width argument is
3.

So, in plain English:

A4 = offset A1 by 0 columns and sum A1:C1
B4 = offset A1 by 3 columns and sum D1:F1
C4 = offset A1 by 6 columns and sum G1:I1
D4 = offset A1 by 9 columns and sum J1:L1


--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
Hi T. Valko,

The formula worked so thank you! :)

I now understand this sum offset function with the exception of this
column
statement:
(COLUMNS($A4:A4)-1)*3
Can you explain how this works?
Specific questions about how this works:
What is the purpose for the column array getting wider as I autofill
the
cells to the right with this formula? (Next cell has $A4:B4 then the
next
cell has $A4:C4, etc...)
What is the purpose of "-1" in this expression
What is the purpose of "*3" in this expression
I know that overall this is expression identifies the 3 columns where
the
values are to be summed, but I don't understand the logic.

In order for me to correctly write and use this formula in the future,
I
need to be able to understand this column expression.

PS I used one year as an example but I have a lot more years per
request
and
then I have often have to do a CY quarterly version and a FY quarterly
version of the monthly tables, so I thought there had to be an easier
way
worth learning. :)

"T. Valko" wrote:

One way:

Assume the first formula is entered in A4:

=SUM(OFFSET($A1,,(COLUMNS($A4:A4)-1)*3,,3))

However, if this is for just a single year which only has 4 quarters,
what's
wrong with using 4 simple sum formulas:

=SUM(A1:C1)
=SUM(D1:F1)
=SUM(G1:I1)
=SUM(J1:L1)

Using those 4 formulas is better than using the formula I suggested.


--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
I am converting a monthly revenue table to a quarterly revenue table.
I
would
like to add A1:C1 and put it the result in a new cell and then
autofill
the
cell to the right of it so that it automatically adds D1:F1 for the
next
value
A B C D E F
1 Jan Feb March April May June
2 10 12 12 13 14 15

Qtr1 Qtr 2 Qtr 3
sum A2:C2 Sum D2:F2 Etc... using autofill
currently using autofill the first qtr is correct "=Sum(A2:C2) but
the
next
cell gets filled with "=sum(B2:D2) instead of adding the next 3
months.

I appreciate anyone's help!