How to sum the last 12 non-zero numbers dynamically by linking to "today" function

[Chukmiester]

I believe I was able to figure out a formula for a perpetual YTD by having excel track today's date which is automatic, but now I am trying to figure out how to have excel now look at the last non-zero cell dynamically and sum the last 12 spaces. i.e. the 7 in the YTD. should be 18; summing the last 12 periods starting with that 7 and if I add a number after that then begin there and count the last 12 and so on.

 

[lrobbo314]

How about this?

Excel
	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI	DJ	DK	DL	DM	DN	DO	DP	DQ	DR	DS	DT	DU	DV	DW	DX	DY	DZ	EA	EB
10	21-Nov	22-Nov	23-Nov	24-Nov	25-Nov	26-Nov	27-Nov	28-Nov	29-Nov	30-Nov	1-Dec	2-Dec	3-Dec	4-Dec	5-Dec	6-Dec	7-Dec	8-Dec	9-Dec	10-Dec	11-Dec	12-Dec	13-Dec	14-Dec	15-Dec	16-Dec	17-Dec	18-Dec	19-Dec	20-Dec	21-Dec	22-Dec	23-Dec		Last 12
11	-	6	2	2	1	1	1	-	-	2	1	-	3	-	7	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-		18
Sheet3

Cell Formulas
Range	Formula
CU10:DZ10	CU10	=CT10+1
EB11	EB11	=SUM(OFFSET(INDEX(CT11:DZ11,0,AGGREGATE(14,6,(COLUMN(CT11:DZ11)-COLUMN(CT11)+1)/(CT11:DZ11>0),1)),,-11,,12))

 

[Alex Blakenburg]

A couple of other options:
(with row 5 being the date row)

By the way unless you use a Total row to "look at the last non-zero cell", I think this is really bad idea.
It is likely that the result would be that each lines' total will cover a different period and this makes the figure misleading.

Using Offset:

=SUM(OFFSET(INDEX($CT$11:$DZ$11,0,MATCH(LOOKUP(2,1/($CT$11:$DZ$11<>0),$CT$5:$DZ$5),$CT$5:$DZ$5,)),,-11,,12))

Without Offset

=SUM(
             INDEX($CT$11:$DZ$11, 0, MATCH( LOOKUP(2,1/($CT$11:$DZ$11<>0), $CT$5:$DZ$5), $CT$5:$DZ$5)) :
             INDEX($CT$11:$DZ$11, 0, MATCH( LOOKUP(2,1/($CT$11:$DZ$11<>0), $CT$5:$DZ$5), $CT$5:$DZ$5 ) - 11))

 

[Peter_SSs]

Welcome to the MrExcel board!

Comments on the above suggestions.

• OFFSET is a volatile function so best avoided if reasonable alternatives exist, which they do here.
• Both the OFFSET formulas are, in my view, risky anyway since early in the year when less than 12 months of data may be available they could incorrectly & without warning include any numerical values to the left of the range being looked at. (Alex's second formula errors in this situation, which at least is a safer option. )
• Alex, I'm sure you wouldn't have meant to use absolute row values for row 11?

I have assumed:

• That no non-zero values will exist forward of the current month.
• That the values in the header row are actual dates being the 1st of each month but formatted to show month & year only.
• That the dashes in the original screen shot are actually zero formatted to show dash.
• That for YTD you would be including Jan-23 back to and including Feb-22.

I'm avoiding OFFSET. Column DU is a formula for YTD, column DV sums 12 columns (or less if they don't exist) back from the last non-zero value.
I have hidden some (zero) columns to keep the mini sheet a bit smaller.

23 01 18.xlsm
	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI										DS	DT	DU	DV
5	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22	Jan-23	Feb-23										Dec-23		YTD	Last 12
6	0	6	2	2	1	1	1	0	0	2	1	0	3	0	7	0										0		18	18
7	5	0	5	4	1	2	0	3	2	8	1	4	7	0	5	0										0		37	37
8	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0										0		0	0
9	3	2	3	2	4	0	0	0	0	0	0	0	0	0	0	0										0		6	14
YTD

Cell Formulas
Range	Formula
DU6:DU9	DU6	=SUMIFS(CT6:DS6,CT$5:DS$5,"<="&TODAY(),CT$5:DS$5,">"&EDATE(TODAY(),-12))
DV6:DV9	DV6	=SUM(INDEX(CT6:DS6,MAX(IFERROR(AGGREGATE(14,6,(COLUMN(CT6:DS6)-COLUMN(CT6)+1)/(CT6:DS6<>0),1),1)-11,1)):DS6)

 

[Alex Blakenburg]

By the way unless you use a Total row to "look at the last non-zero cell", I think this is really bad idea.
It is likely that the result would be that each lines' total will cover a different period and this makes the figure misleading.

Using Peter's data and referring to the last 12 mths column:
• Rows 6 & 7 - Sums Feb 2022 to Jan 2023
• Row 9 - Sums Nov 2021 to Mar 2022 (This is only 5 months)
Not only is the 12 month's ending Mar 2022 a different last 12 month period to other rows which I don't think your users will be expecting but in this case it is only 5 months not 12 months (per the heading).
So I personally would not be comfortable this approach.

 

[Peter_SSs]

Using Peter's data and referring to the last 12 mths column:

Just to clarify: My post does not have a 'last 12 mths' column. It is simply a 'Last 12' column, based on:

now I am trying to figure out how to have excel now look at the last non-zero cell dynamically and sum the last 12 spaces.

I took the expression "last non-zero cell" to mean 'last non-zero cell for the current row' whereas you are considering it might mean 'last non-zero cell in any row'.
Since we don't really know what the various rows of data represent or what the OP is actually trying to do so I think that your caution/warning is well-advised.

Hopefully we will find out in due course.

 

[Peter_SSs]

it might mean 'last non-zero cell in any row'.

If the goal is to add 12* columns always counting back from the column that has the last non-zero value in any row (column DG in my sample below) then column DW has a suggestion for that.
* .. or less than 12 columns if the last non-zero column in the 'longest' row is less than 11 columns to the right of the first column in the range.

23 01 18.xlsm
	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI										DS	DT	DU	DV	DW
5	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22	Jan-23	Feb-23										Dec-23		YTD	Last 12	Last 12 (2)
6	0	6	2	2	1	1	1	0	0	2	1	0	3	0	0	0										0		11	19	13
7	5	8	5	4	1	2	0	3	2	8	1	4	7	8	0	0										0		40	45	45
8	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0										0		0	0	0
9	3	2	3	2	4	0	0	0	0	0	0	0	0	0	0	0										0		6	14	9
YTD

Cell Formulas
Range	Formula
DU6:DU9	DU6	=SUMIFS(CT6:DS6,CT$5:DS$5,"<="&TODAY(),CT$5:DS$5,">"&EDATE(TODAY(),-12))
DV6:DV9	DV6	=SUM(INDEX(CT6:DS6,MAX(IFERROR(AGGREGATE(14,6,(COLUMN(CT6:DS6)-COLUMN(CT6)+1)/(CT6:DS6<>0),1),1)-11,1)):DS6)
DW6:DW9	DW6	=SUM(INDEX(CT6:DS6,MAX(IFERROR(AGGREGATE(14,6,(COLUMN(CT6:DS6)-COLUMN(CT6)+1)/(CT$6:DS$9<>0),1),1)-11,1)):DS6)

 

[Chukmiester]

Thank you all kindly for such prompt responses.

 

[Chukmiester]

Welcome to the MrExcel board!

Comments on the above suggestions.

• OFFSET is a volatile function so best avoided if reasonable alternatives exist, which they do here.
• Both the OFFSET formulas are, in my view, risky anyway since early in the year when less than 12 months of data may be available they could incorrectly & without warning include any numerical values to the left of the range being looked at. (Alex's second formula errors in this situation, which at least is a safer option. )
• Alex, I'm sure you wouldn't have meant to use absolute row values for row 11?

I have assumed:

• That no non-zero values will exist forward of the current month.
• That the values in the header row are actual dates being the 1st of each month but formatted to show month & year only.
• That the dashes in the original screen shot are actually zero formatted to show dash.
• That for YTD you would be including Jan-23 back to and including Feb-22.

I'm avoiding OFFSET. Column DU is a formula for YTD, column DV sums 12 columns (or less if they don't exist) back from the last non-zero value.
I have hidden some (zero) columns to keep the mini sheet a bit smaller.

23 01 18.xlsm
	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI										DS	DT	DU	DV
5	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22	Jan-23	Feb-23										Dec-23		YTD	Last 12
6	0	6	2	2	1	1	1	0	0	2	1	0	3	0	7	0										0		18	18
7	5	0	5	4	1	2	0	3	2	8	1	4	7	0	5	0										0		37	37
8	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0										0		0	0
9	3	2	3	2	4	0	0	0	0	0	0	0	0	0	0	0										0		6	14
YTD

Cell Formulas
Range	Formula
DU6:DU9	DU6	=SUMIFS(CT6:DS6,CT$5:DS$5,"<="&TODAY(),CT$5:DS$5,">"&EDATE(TODAY(),-12))
DV6:DV9	DV6	=SUM(INDEX(CT6:DS6,MAX(IFERROR(AGGREGATE(14,6,(COLUMN(CT6:DS6)-COLUMN(CT6)+1)/(CT6:DS6<>0),1),1)-11,1)):DS6)

Hello, you seem to be onto something. Your formula almost worked. The first assumption is not correct and that is my fault. There may actually be zero numbers going forward. The dates are the first of each month.

 

[Peter_SSs]

Your formula almost worked.

Which formula (I posted 3 of them)?

You would also have seen discussion in the thread about two different interpretations of your statement " look at the last non-zero cell dynamically and sum the last 12 spaces."
You have not clarified which is the correct one. Please do so. That is, for row 6 in my last mini sheet, is the correct answer
19 - finding the last non-zero value in row 6 (Nov-22) and summing row 6 twelve columns back from the Nov-22 column
or
13 - finding the last non-zero value in any row (Dec-22 in row 7) and summing row 6 twelve columns back from the Dec-22 column
or
something else?