Can someone help please
I colate the amount of sick days someone has off each month, what i would
like to be able to do is put in a calculation or something that will tell me
how many over the previous 12 months and then each new month it adds the
current month and drops of the oldest month to create a new total 12 month
rolling figure

Hi,

It would be a great deal easier if the data layout was available but here's
something you could adapt maybe:-

A B
Jan-06 3
Feb-06 5
Mar-06 6
Apr-06 7
May-06 4
Jun-06 5
Jul-06 5
Aug-06 4
Sep-06 12
Oct-06 12
Nov-06 3
Dec-06 4
Jan-07 5
Feb-07 6
Mar-07 78
Apr-07 7
May-07 6
Jun-07 5
Jul-07 4

Which are the months which you will add to and the days absence. Somewhere
else enter the formula:-

=SUMIF(A1:A100,"<"&TODAY(),B1:B100)-(SUMIF(A1:A100,"<"&TODAY()-365,B1:B100))

For today the formula would add the current July and the previous 11 months
and if anyone has a record this bad then dismissal is an option:)

Mike



"louiscourtney" wrote:

Can someone help please
I colate the amount of sick days someone has off each month, what i would
like to be able to do is put in a calculation or something that will tell me
how many over the previous 12 months and then each new month it adds the
current month and drops of the oldest month to create a new total 12 month
rolling figure

Mike that looks perfect, the only problem i have
I have each year on a differant tab what part of the sum should i change to
pick up the other tab?

Thanks for your help so far
Shane

"Mike H" wrote:

Hi,

It would be a great deal easier if the data layout was available but here's
something you could adapt maybe:-

A B
Jan-06 3
Feb-06 5
Mar-06 6
Apr-06 7
May-06 4
Jun-06 5
Jul-06 5
Aug-06 4
Sep-06 12
Oct-06 12
Nov-06 3
Dec-06 4
Jan-07 5
Feb-07 6
Mar-07 78
Apr-07 7
May-07 6
Jun-07 5
Jul-07 4

Which are the months which you will add to and the days absence. Somewhere
else enter the formula:-

=SUMIF(A1:A100,"<"&TODAY(),B1:B100)-(SUMIF(A1:A100,"<"&TODAY()-365,B1:B100))

For today the formula would add the current July and the previous 11 months
and if anyone has a record this bad then dismissal is an option:)

Mike



"louiscourtney" wrote:

Can someone help please
I colate the amount of sick days someone has off each month, what i would
like to be able to do is put in a calculation or something that will tell me
how many over the previous 12 months and then each new month it adds the
current month and drops of the oldest month to create a new total 12 month
rolling figure

How are your tabs named ? (And I assume data is Jan to Dec on each)

"louiscourtney" wrote:

Mike that looks perfect, the only problem i have
I have each year on a differant tab what part of the sum should i change to
pick up the other tab?

Thanks for your help so far
Shane

"Mike H" wrote:

Hi,

It would be a great deal easier if the data layout was available but here's
something you could adapt maybe:-

A B
Jan-06 3
Feb-06 5
Mar-06 6
Apr-06 7
May-06 4
Jun-06 5
Jul-06 5
Aug-06 4
Sep-06 12
Oct-06 12
Nov-06 3
Dec-06 4
Jan-07 5
Feb-07 6
Mar-07 78
Apr-07 7
May-07 6
Jun-07 5
Jul-07 4

Which are the months which you will add to and the days absence. Somewhere
else enter the formula:-

=SUMIF(A1:A100,"<"&TODAY(),B1:B100)-(SUMIF(A1:A100,"<"&TODAY()-365,B1:B100))

For today the formula would add the current July and the previous 11 months
and if anyone has a record this bad then dismissal is an option:)

Mike



"louiscourtney" wrote:

Can someone help please
I colate the amount of sick days someone has off each month, what i would
like to be able to do is put in a calculation or something that will tell me
how many over the previous 12 months and then each new month it adds the
current month and drops of the oldest month to create a new total 12 month
rolling figure

Try:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$H$1:$H$2&"'!A1:A10 0"),"<="
&TODAY(),INDIRECT("'"&$H$1:$H$2&"'!B1:B100"))-SUMIF(INDIRECT("'"&$H$1:$H$2&"'!A1:A100"),"<=" &TODAY()-365,INDIRECT("'"&$H$1:$H$2&"'!B1:B100")))

Put Mike's 06 data on Sheet1 anf 07 on Sheet2. Put "Sheet1" in H1 (no
quotes) and "Sheet2" in H2.

(It then needs adapting for your sheet names)

HTH

"Toppers" wrote:

How are your tabs named ? (And I assume data is Jan to Dec on each)

"louiscourtney" wrote:

Mike that looks perfect, the only problem i have
I have each year on a differant tab what part of the sum should i change to
pick up the other tab?

Thanks for your help so far
Shane

"Mike H" wrote:

Hi,

It would be a great deal easier if the data layout was available but here's
something you could adapt maybe:-

A B
Jan-06 3
Feb-06 5
Mar-06 6
Apr-06 7
May-06 4
Jun-06 5
Jul-06 5
Aug-06 4
Sep-06 12
Oct-06 12
Nov-06 3
Dec-06 4
Jan-07 5
Feb-07 6
Mar-07 78
Apr-07 7
May-07 6
Jun-07 5
Jul-07 4

Which are the months which you will add to and the days absence. Somewhere
else enter the formula:-

=SUMIF(A1:A100,"<"&TODAY(),B1:B100)-(SUMIF(A1:A100,"<"&TODAY()-365,B1:B100))

For today the formula would add the current July and the previous 11 months
and if anyone has a record this bad then dismissal is an option:)

Mike



"louiscourtney" wrote:

Can someone help please
I colate the amount of sick days someone has off each month, what i would
like to be able to do is put in a calculation or something that will tell me
how many over the previous 12 months and then each new month it adds the
current month and drops of the oldest month to create a new total 12 month
rolling figure

Thanks Toppers
I should have made myself clearer from the start
I will try to explain

One tab named history with names down one side in column A and then the
months going across the top Jan, Feb,mar,Apr,May etc etc with a totals column
at the end
Then there is the current year tab with the same names down one side and the
months across the top.
What i need to know is the calculation that links both sheets giving me a
riolling 12 month figure

Thanks to anyone that can solve this for me

"Toppers" wrote:

Try:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$H$1:$H$2&"'!A1:A10 0"),"<="
&TODAY(),INDIRECT("'"&$H$1:$H$2&"'!B1:B100"))-SUMIF(INDIRECT("'"&$H$1:$H$2&"'!A1:A100"),"<=" &TODAY()-365,INDIRECT("'"&$H$1:$H$2&"'!B1:B100")))

Put Mike's 06 data on Sheet1 anf 07 on Sheet2. Put "Sheet1" in H1 (no
quotes) and "Sheet2" in H2.

(It then needs adapting for your sheet names)

HTH

"Toppers" wrote:

How are your tabs named ? (And I assume data is Jan to Dec on each)

"louiscourtney" wrote:

Mike that looks perfect, the only problem i have
I have each year on a differant tab what part of the sum should i change to
pick up the other tab?

Thanks for your help so far
Shane

"Mike H" wrote:

Hi,

It would be a great deal easier if the data layout was available but here's
something you could adapt maybe:-

A B
Jan-06 3
Feb-06 5
Mar-06 6
Apr-06 7
May-06 4
Jun-06 5
Jul-06 5
Aug-06 4
Sep-06 12
Oct-06 12
Nov-06 3
Dec-06 4
Jan-07 5
Feb-07 6
Mar-07 78
Apr-07 7
May-07 6
Jun-07 5
Jul-07 4

Which are the months which you will add to and the days absence. Somewhere
else enter the formula:-

=SUMIF(A1:A100,"<"&TODAY(),B1:B100)-(SUMIF(A1:A100,"<"&TODAY()-365,B1:B100))

For today the formula would add the current July and the previous 11 months
and if anyone has a record this bad then dismissal is an option:)

Mike



"louiscourtney" wrote:

Can someone help please
I colate the amount of sick days someone has off each month, what i would
like to be able to do is put in a calculation or something that will tell me
how many over the previous 12 months and then each new month it adds the
current month and drops of the oldest month to create a new total 12 month
rolling figure