Need Sum without using ROW 1

[briangriffin]

Need to sum the latest three values from a rolling 12 month data feed:

3/1 100
4/1 200
5/1 300
6/1 400


The additional 8 rows won't be populated fully until next January, but from that point on there will always be 12. Then I could just sum B10:B12, but for now I need a formula to grab the last three (in the example it would return 900).

The catch is that I'm using Xcelsius, so the ROW function isn't supported. I see some other examples online that are similar but not quite what I need, and I'm having trouble translating them into my requirement. MATCH seems to be the foundation of the workaround.

Thanks in advance.

 

[mintjulep]

SUMIF

With the IF being that the month in column A is >= month(now())

 

[SkipVought]

hi,

The additional 8 rows won't be populated fully until next January

Not a very sound design to have pre-entered not to be used values.

If your summary table were a STRUCTURED TABLE (Insert > Tables > Table) then each period you simply add a new row at the bottom and all your formulas will automatically populate.

I'd use the OFFSET() function on the Total column like this, assuming that your Structured Table is named tSUM, and the column heading is Total...
[tt]
=SUM(OFFSET(tSUM[Total],COUNT(tSUM[Total])-3,0,3,1))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[briangriffin]

That did it, thanks. Nothing is prepopulated, the future rows are blank.

 

[SkipVought]

Then the word "fully" must be totally superfluous!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}