Attendance on a rolling calendar

I have an attendance calendar at my job that I have modified a little to suit our needs. One thing I have been unable to do is calculate the Occurences (J31) on a rolling calendar. I am pulling the info from Column H in the table on the second tab. I dont care if it is a COUNTIF or SUMIF statement. I can always change the x's to a 1 if needed, as long as it only records info for a rolling calendar year.

I have attached a sample so that you can see what I am working with.

Thanks

I believe that worked. Thanks

Let me retract...this will look at only the current year. I need to calculate based on a rolling year. For example...given todays date of 12/2/2014...Joe Smith would have 2 occurences from last year and 4 occurences from this year for a total of 6. Does this help clarify?

yes, i want to look at 1 years worth of time from today's date.

the option to select the year is to show the calendar for the current year. I can always add a hidden cell in A1 that always shows the current date when opened. this might help the formula.

Originally Posted by mcmahobt

No helper cell should be necessary. I think this should do the trick:

Please Login or Register  to view this content.

Hope this helps.

One last question. We are making adjustments, and now instead of an X, we will be using numbers (1, .5, .25, etc). How would you adjust the formula to count the add up the occurences instead of counting the x's?

Thanks

That did not seem to work. I may have mistyped in my previous post. We are wanting to add up the total occurrences, not count them. We need a sum total on a rolling calendar year.

I can use a SUMIF statement, but then I have trouble figuring out the rolling calendar year (since we only count absences for 1 year)

Now im just being a pain in the butt...lol

I have attached the sheet I am working with because I think I am missing something in my attempt to explain it to you.

On the Employee Leave Tracker, this employee has a total of 13 days, but only 11 occurrences, based on the numbers provided.

However, on the CTO Summary, the formula is counting the 13 days instead of adding the occurrences. I would need cell K124 in the CTO summary to show the actual 11 occurrences for this employee, for the previous 12 months.


Attendance 2014-2015 test.xlsx