Assigning Values to Text

[Nickdel87]

Hello,
I am trying to assign values to text in multiple cells. I am trying to make an employee attendance tracker less manual. For example when I fill a cell that represents a day on a calendar I would use the word "sick" when they call out. How do I make that word "sick" equal to a value of 8 so that I can use the SUM formula and keep a running tally? I would also use other codes such as vacation time or whatever it may be. Can I assign these codes with values?

 

[Fluff]

Why not switch it round, enter 8 into the cell & then use conditional formatting to show "Sick"

 

[sturnusek]

Hi,

Try the below, I would use another cell to calculate any anomalies.

=IF(OR(A9="Sick",A9="Holiday"),"8","")

Thanks

 

[steve the fish]

Id create a lookup table so i could change it as and when. Then use a helper column using a vlookup formula. If it was to never change you could use a formula like:

=SUM(IF(A1:A10={"Sick","Half Day"},{4,8}))

which needs CTRL-SHIFT-ENTER. However you have to be very careful of spelling mistakes then.

 

[Nickdel87]

Why not switch it round, enter 8 into the cell & then use conditional formatting to show "Sick"

I would need multiple codes to be equal to 8. Would that still work?

 

[Fluff]

In that case no it wouldn't.
I'd go with either of Steve's solutions