Formula needed for Sum(A1:C1) then Sum(D1:F1) in next cell

[Steve]

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(B22) instead of adding the next 3 months.

I appreciate anyone's help!

 

[T. Valko]

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.

 

[Ken Wright]

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]

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.

 

[Steve]

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

 

[T. Valko]

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($A44) = 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($A44)-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

 

[Steve]

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]

You're welcome. Thanks for the feedback!