Hi

It is preferable to post your question in a single group. I responded to
your briefer question in Worksheet.functions asking for more
information.

On the basis of the information provided here, with 4 rows being used
for each Date's entry, and the data going down the column for each
person, then I assume in a full year there will be 1040 rows of data (52
weeks x 5 days x 4 rows) beginning with row 2 in each case and extending
to row 1041.

This being the case the formula to calculate the number of days in the
current year is simply
=COUNTIF(A$2:A$1041,"Unexcused Absence")
This could be placed in say cell A1044
Since there will only ever be data up to and including today's date,
this is always the answer for the current year.

To obtain a figure for rolling 12 months, then to the value outlines
above, you need the number of days that took place after today's date
one year previous, up to the end of that year.
In cell A1045 enter
=SUMPRODUCT(--(A5:A1041="Unexcused Absence"),
--(A2:A1038DATE(year(today())-1,MONTH(TODAY()),DAY(TODAY()))))

The 2 arrays are of equal length, but offset by 3 rows to account for
the reason being 3 rows below the date.
In the current year, this value will always be 0, but in the previous
year it will hold the number of days required to be added to the current
year to create a rolling year figure..

The Rolling year will be the sum of the 2 formulae so on Sheet 2007 in
cell A1046
=A1044+'Sheet 2006'!A1045


--
Regards

Roger Govier


"QD" wrote in message
oups.com...
I need help.

I have created an attendance calendar for 2006 and 2007 under two
different tabs for each employee. For each day there are four rows
where the first row is a date, second row is for code for attendance
and hours (has two cells), the third row has indication if it is
considered an occasion or not based on the attendace reason in the row
4th.

11/13/06 13
TPU 8
Occ
Unexcused Absence (Whole)

Now at the bottom in summary I need to count number of Unexcused
absences based on 12 rolling months. So I need a formula for 2007
calendar in summary where based on a current date in 2007, the formula
goes back 12 months and looks for "Unexcused Absence" and count total
occurances.

For example today is March 15th 2007. So the formula will go back
twelve months, 3 months in 2007 calendar and 9 months in 2006 calendar
and look for "Unexcuse.." and count and place the number. If it sees
Unexcuse... 5 times it will place number "5" in the summary.

Can someone hlep me. I have a temporary solution but it is not the
best
one. What I have done is created a table for twelve months in 2006
calendar where it copies the "unexcuse.." in the table under the date
when I enter it "Unexused..." in 2006 calendar. Then in a second
column
I have a formula that looks at todays date and the date for which the
"Unexcus.." appears, if it is 12 months it deletes it. Than at the
end
of the year I will copy the whole table in the same sheet with 2007
calendar. Then I have a formula for counting all "unsched" in 2007
calendar and than add sum from the table for 2006 based on the current
date. The formula for the sum of 2006 drops each day based on todays
date. So the sum for 2006 changes and it adds to the 2007 totals.


Please help simplify this by creating a formula in 2007 calendar that
goes back 12 months and looks in 2007 and 2006 calendar and cout
number
of times the occurance is entered.

Thanks
QD