Formula that assigns numerical values to specific text cell entries and provides a total

Hi there,

I am quite new to Excel so would be very grateful for any help on a problem that I am trying to solve. I am creating a holiday tracker spreadsheet for work and would like to easily total the number of full and half days that a particular individual is taking off for annual leave. I am also tracking sick days and personal leave. I would like to assign certain values for various cell text entries, such as:

* H HD (Holiday Half Day) = 0.5
* H FD (Holiday Full Day) = 1
* S HD (Sickness Half Day = 0.5
* S FD (Sickness Full Day) = 1

At the beginning of the row, I would like to have a sum total of the amount of days that each individual has taken in a given time period. I have created worksheets broken out into quarters, with columns denoting the working days in each month. I would like to enter in the specific text value of 'H HD', 'H FD', etc. within the related cell so as to denote what type of leave is being taken at which date by each individual. I have used conditional formatting for colour coding already.

I am stumped as to how to assign a particular value to the text entry and then have the total sum appear in a cell. Can anyone provide any suggestions for this?

Many thanks,

Daniel

You could have this in B7:

=COUNTIF($E7:$BP7, "H FD") + COUNTIF($E7:$BP7, "H HD")/2

then copy down. Similar formulae could be used for S FD and S HD. You will need to validate each cell to ensure that only the pre-defined codes are used.

Hope this helps.

Pete

Hi Pete,

This is incredibly helpful, the formula works perfectly for what I was hoping to achieve. Thank you so so much!

Daniel