Year to Date Totals - Automatic formula adjusting

[rgillson3]

I've got a spreadsheet containing customer counts for each period of various years.

As part of our periodic review process, we look at the year to date counts for a given period, compared to the same time frame for previous years.

Currently, I adjust nine rows to include only the desired periods. This is done for six different revenue centers.

Is there a way to have the spreadsheet auto-adjust the formula for previous years, based on whether or not there is data for the current year.

Let's say periods are in columns A thru M (13 periods).

Period 10 is in column J, and the current year is in row 16.

I'd like row 15 to automatically sum columns A thru J.

Then, when period 11 in column K is populated, I'd want the previous years to adjust to also include column K, and so on.

 

[jorismoerings]

Hi,

Two options, try which one works best for you.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
12
13
14															165
15	10	11	12	13	14	15	16	17	18	19	20	21	22		165
16	10	10	10	10	10	10	10	10	10	10	10
17
Sheet1

Cell Formulas
Range	Formula
O14	=SUM(OFFSET(A15,0,0,1,13-COUNTBLANK(A16:M16)))
O15	=SUMIFS(A15:M15,A16:M16,"<>"&"")

 

[rgillson3]

Thank you.

At a glance, I think either of those would work.

However, initially I forgot that I "complicated" my spreadsheet. Each of our periods of four weeks in length, and I've added columns for each of the weeks.

Sooooo, I'm going to have to play with the formulas to see if I can get one of them to work for me.

Thank you again.

 

[rgillson3]

Update:

I was able to adjust the SUMIFS formula, to include my "helper" columns, and it seems to work well.