# How do I allocate different text cells in a row a number and then sum total that row

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.

## 2 Answers

This is a bit of sleight-of-hand, but assuming your four codes for leave types are fixed, it should serve your purposes: 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.

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.