Add Totals for Last 12 Months
 

Hi,
I have monthly totals for several years. Want to keep all totals but only
add the most recent 12 month total. Is there a quick way to do that vs.
revising the formula monthly?

Example:

Cell: A2 B2 C2 D2 E2 F2 G2 H2 I2 J2 K2 L2 M2 N2 O2 P2
#: 1 3 2 1 2 2 1 2 1 3 1 1 2 3 2
2

1st Formula: =SUM(A2:L2)
Next Formula: =SUM(B2:M2)

What's the easy way to shift the 12 cells 1 cell to the right?

Many thanks, in advance, for your help.

Add Totals for Last 12 Months
 

This will give the total of the last 12 months:

=SUM(OFFSET(A2,0,MAX(0,COUNT(2:2)-12),1,12))

OR

in A3 will give A2:L22
=SUM(OFFSET($A$2,0,COLUMN()-1,1,12))

in B3 will give B2:M22
=SUM(OFFSET($A$2,0,COLUMN()-1,1,12))


HTH


"Beamers" wrote:

Hi,
I have monthly totals for several years. Want to keep all totals but only
add the most recent 12 month total. Is there a quick way to do that vs.
revising the formula monthly?

Example:

Cell: A2 B2 C2 D2 E2 F2 G2 H2 I2 J2 K2 L2 M2 N2 O2 P2
#: 1 3 2 1 2 2 1 2 1 3 1 1 2 3 2
2

1st Formula: =SUM(A2:L2)
Next Formula: =SUM(B2:M2)

What's the easy way to shift the 12 cells 1 cell to the right?

Many thanks, in advance, for your help.

Add Totals for Last 12 Months
 

Toppers,
HUGE thanks for your quick response. The 2 formulas after "OR" worked fine.
Thanks again.

The first one at the top didn't work. It was referring to a circular
reference. I don't know what that means. It was showing 0 in the cell with
the formula.

"Toppers" wrote:

This will give the total of the last 12 months:

=SUM(OFFSET(A2,0,MAX(0,COUNT(2:2)-12),1,12))

OR

in A3 will give A2:L22
=SUM(OFFSET($A$2,0,COLUMN()-1,1,12))

in B3 will give B2:M22
=SUM(OFFSET($A$2,0,COLUMN()-1,1,12))


HTH


"Beamers" wrote:

Hi,
I have monthly totals for several years. Want to keep all totals but only
add the most recent 12 month total. Is there a quick way to do that vs.
revising the formula monthly?

Example:

Cell: A2 B2 C2 D2 E2 F2 G2 H2 I2 J2 K2 L2 M2 N2 O2 P2
#: 1 3 2 1 2 2 1 2 1 3 1 1 2 3 2
2

1st Formula: =SUM(A2:L2)
Next Formula: =SUM(B2:M2)

What's the easy way to shift the 12 cells 1 cell to the right?

Many thanks, in advance, for your help.