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!