1

I have a spreadsheet setup to monitor absence in the office. The columns are the days of the month 1 - 31 and rows are the month/employees

        Mon Tue Wed Thu Fri

January 1 2 3 4 Jeanne
Jan
Ben
Ellie
Alice
Stef
Victoria
February
Jeanne
Jan
Ben
Ellie
Alice
Stef
Victoria

The absences are listed as AL, AL.5, SL & SL.5 = Annual Leave, Annual Leave Half Day, Sick Leave & Sick Leave Half Day

I want to be able to add a total column for annual leave and sick and create a formula that sums the total number of days and half days in each row.

I am having trouble make any formula recognise the AL.5 & SL.5 as a value of .5. I have tried adding a criteria table but I cannot get the formula to work.I am currently using excel 2013.

1

This is a bit of sleight-of-hand, but assuming your four codes for leave types are fixed, it should serve your purposes:

Excel sheet snip

The text in cell H2 is the formula I entered into cell F2, and then copied into the range F2:G4. The first COUNTIF counts the number of occurrences of the appropriate "AL" or "SL" code, and the second counts the number of "AL.5" or "SL.5" codes. I just multiplied the second count by 0.5 to get the weighted sum you're looking for.

0

I would use the Power Query Add-In for this. Power Query can import the data from an Excel Table. I would use the Unpivot command to transform each cell into a row, with a single values column holding all the absences values (e.g. AL, SL.5). Then I would use the Split command on that column to separate the first 2 characters (AL, SL) from the .5 values. I would rename the column with the 1st 2 characters to Absence type, and the new column to Days. Then I would use the Replace command to replace any blank values in the Days column with 1. Then I would convert the Days column data type to a Decimal Number.

You could use the Group By command at this point to summarize the data and Sum the Days column. I would probably just deliver the query in detailed form into the Excel Data Model, then build a Pivot Table on-top of it. This will give you more analytical power as you can freely sum, filter, slice and dice etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.