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