Sum Of Last Three Entries In Rotating Columns

[Loadmaster]

Hi, I entered the years in row 4, 2006, 2007 etc… starting in E4 and working
right. I then entered Jan thru to Dec in Column D, starting at D5. At the end
of each month I enter a value within the spreadsheet Corresponding to the
Month and Year. How do I get the sum of the last three months when the value
data is entered down one column and then starts back up and down the next
column to the right?

 

[daddylonglegs]

So let's assume that your years extend as far as J4 and therefore your
numbers are in E5:J16. This formula will sum the last 3 entries irrespective
of the current date:


=SUM(IF(COLUMN(E5:J16)*100+ROW(E5:J16)>LARGE((COLUMN(E5:J16)*100+ROW(E5:J16))*(E5:J16<>""),4),E5:J16))

This is an "array formula" which must be confirmed with CTRL+SHIFT+ENTER so
that curly braces like { and } appear around the formula in the formula bar

Note: assumes that there won't be gaps in the data

 

[Loadmaster]

That formula worked great, however I have one more question WRT the same
spreadsheet. How do I retrieve the last entry from the last row and column?

 

[Peo Sjoblom]

=LOOKUP(2,1/(A1:A20000<>""),A1:A20000)

for the last row in A1:A20000

adapt to fit


=LOOKUP(2,1/(2:2<>""),2:2)

last column in row 2


--


Regards,


Peo Sjoblom

 

[Loadmaster]

What I meant to say was enter the row within the last column not both the
last column and last row.

 

[daddylonglegs]

For the last entry only try

=LOOKUP(9.999999999999E+307,INDEX(E5:J16,0,MAX(IF(E5:J16<>"",COLUMN(E5:J16)-COLUMN(E5)+1))))

 

[Loadmaster]

Thank-you, that formula also worked great.

For the last entry only try

=LOOKUP(9.999999999999E+307,INDEX(E5:J16,0,MAX(IF(E5:J16<>"",COLUMN(E5:J16)-COLUMN(E5)+1))))