How do I add up the last 12 cells (containing values) in a column.
 

I have a table of hours worked by temporary staff which I am constantly
adding new values to. I need to calculate average hours worked from the last
12 weeks they worked at any given moment so I want a total of there hours for
the last 12 weeks.

I have limited knowledge of excel - so at the moment - I add the latest
value - then recalculate the sum of those 12 cells - I would like a table
that automatically calculates the sum of the last 12 cells in a column -
whatever cells they are.

Hope you can help,

Michelle

How do I add up the last 12 cells (containing values) in a column.
 

Hi,

Try this

=SUM(OFFSET(A1,COUNTA(A:A)-12,):OFFSET(A1,COUNTA(A:A),))

If there are less than 12 valuse in the range the formula will give an error.

Mike

"Michelle D" wrote:

I have a table of hours worked by temporary staff which I am constantly
adding new values to. I need to calculate average hours worked from the last
12 weeks they worked at any given moment so I want a total of there hours for
the last 12 weeks.

I have limited knowledge of excel - so at the moment - I add the latest
value - then recalculate the sum of those 12 cells - I would like a table
that automatically calculates the sum of the last 12 cells in a column -
whatever cells they are.

Hope you can help,

Michelle

How do I add up the last 12 cells (containing values) in a col
 

Thank you very much!

"Mike H" wrote:

Hi,

Try this

=SUM(OFFSET(A1,COUNTA(A:A)-12,):OFFSET(A1,COUNTA(A:A),))

If there are less than 12 valuse in the range the formula will give an error.

Mike

"Michelle D" wrote:

I have a table of hours worked by temporary staff which I am constantly
adding new values to. I need to calculate average hours worked from the last
12 weeks they worked at any given moment so I want a total of there hours for
the last 12 weeks.

I have limited knowledge of excel - so at the moment - I add the latest
value - then recalculate the sum of those 12 cells - I would like a table
that automatically calculates the sum of the last 12 cells in a column -
whatever cells they are.

Hope you can help,

Michelle

How do I add up the last 12 cells (containing values) in a column.
 

One way to find the last row and count back.
=SUM(INDIRECT("a"&MATCH(999999,A:A)-11&":a"&MATCH(9999999,A:A)))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Michelle D" <Michelle wrote in message
...
I have a table of hours worked by temporary staff which I am constantly
adding new values to. I need to calculate average hours worked from the
last
12 weeks they worked at any given moment so I want a total of there hours
for
the last 12 weeks.

I have limited knowledge of excel - so at the moment - I add the latest
value - then recalculate the sum of those 12 cells - I would like a table
that automatically calculates the sum of the last 12 cells in a column -
whatever cells they are.

Hope you can help,

Michelle

How do I add up the last 12 cells (containing values) in a col
 

Shorter version:

=SUM(OFFSET(A1,COUNT(A:A)-12,,12,))


"Mike H" wrote:

Hi,

Try this

=SUM(OFFSET(A1,COUNTA(A:A)-12,):OFFSET(A1,COUNTA(A:A),))

If there are less than 12 valuse in the range the formula will give an error.

Mike

"Michelle D" wrote:

I have a table of hours worked by temporary staff which I am constantly
adding new values to. I need to calculate average hours worked from the last
12 weeks they worked at any given moment so I want a total of there hours for
the last 12 weeks.

I have limited knowledge of excel - so at the moment - I add the latest
value - then recalculate the sum of those 12 cells - I would like a table
that automatically calculates the sum of the last 12 cells in a column -
whatever cells they are.

Hope you can help,

Michelle