I could be nuts, but am trying to devise a simple 12-month on a page excel calendar on which I can record employee attendance using a drop-down menu (with conditional formatting) for various absences.



For example, S is for Sick Days, T is for Tardy, V is for vacation, and s is for .5 of a sick day, t is for .5 of a tardy day, v is for .5 of a vacation day, etc.

OK, got the drop-down calendar in hand, now - I want to be able to calculate the sum of: all S days (where S = 1, s = .5); T days (where T = 1, t = .5), etc., and calculate the total for the various months on a quarterly basis (i.e. 3 months across the page), and ultimately on a yearly basis (i.e. 4 rows of 3 months each).

The quarterly formulas are stumping me, big time!

It might also be a problem that the capital/lower case difference may not be recognizable in the formula (i.e. that it might not be possible to differentiate between a capital 'S' and a lower case 's', in which case - I'll have to come up with some other text for the half day record).

I THINK some kind of 'SUMIFS' formula is required, but I don't have enough experience to work it through.

Can anyone help me?

Thank you!

Attached Files

Attendance 2 (Recovered).xlsx (29.2 KB, 15 views)