Sum of last "x" values across columns

[Space_Cowboy]

Please see below. Row 3 is the header row. data contained in (b3:c12). I am looking for a way to automatically look for the last 12 populated cells in (b3:c12), and place the result in d13. for example, in the below sample data, d13 should sum (C4:C9)+(B7:B12) for a value of 71. When I later add data to cell c10, I need for d13 to now sum (C4:C10)+(b8:b12), and so on. When the data is down to d12, the next data value will be in d4 and the calculation in d13 will appear in e13. Is therre a way to do this?


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2016[/TD]
[TD]2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]9[/TD]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD]ddd[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[StephenCrump]

Re: Sum of last "x" values avross columns

It looks like you want to sum the most recent 12 months' values, but there are only 9 rows of data for each month.

Is that right? Which means that when you have one value for 2018, you'll be summing:

- that one value for 2018, plus
- all 9 values for 2017, plus
- the last two values for 2016?

 

[inactiveuserps07]

Re: Sum of last "x" values avross columns

[post withdrawn for further review]

 

[Space_Cowboy]

Re: Sum of last "x" values avross columns

My mistake in the table. There will never be more than 2 columns used. So in June of 2018 for instance, I want to pull Jan through Jun of 2018 and also July through December of 2017.

 

[StephenCrump]

Re: Sum of last "x" values avross columns

C19: =SUMPRODUCT(MyData*(DATE(Years,Months,1)<=RequiredDate)*(DATE(Years,Months,1)>=1+EDATE(RequiredDate,-N)))

where:

RequiredDate: = C17 (entered as a date, not text)
n: = C18
MyData: =C4:E15
Years: =C3:E3
Months: =B4:B15

Excel 2010
	B	C	D	E
3	Month	2016	2017	2018
4	1	5	4
5	2	3	4
6	3	4	5
7	4	2	7
8	5	9	12
9	6	1	12
10	7	4
11	8	8
12	9	3
13	10	6
14	11	7
15	12	11
16
17	Sum to	Jun 2017
18	No months	12
19	Result	83
Sheet1

 

[Tetra201]

Re: Sum of last "x" values avross columns

... I am looking for a way to automatically look for the last 12 populated cells in (b3:c12)...

... There will never be more than 2 columns used. So in June of 2018 for instance, I want to pull Jan through Jun of 2018 and also July through December of 2017.

If your 2-year, 12-month monthly data reside in range B4:C15 and if there are no unpopulated cells before the latest entry, then try:

=SUM(C4:C15)+SUMIF(C4:C15,"",B4:B15)