sum letters with values and numbers

[Galadad]

I have a time sheet that where I want to do various totals. When it's hours
worked, no problem. This department also uses letters (P, V, H) to indicate
time off with pay (personal, vacation, holiday). They would also like to use
the lower case version to indicate half a day. How do a get a sum or sumif
function to see the letters as their numeric equivalent and total them with
the other numbers entered?

I know this is probably overly simple for this site, but I can't find it in
the Help section or I can't phrase the question will enough for Help to find
my answer.

Thanks,

 

[Bernard Liengme]

Since most Excel functions are case insensitive, I think using H for one
thing and h for another is adding a horrid complication,
How about H and HH ?
With this in A1:J1
h , , h, h, , 3, hh, 1, 2, 3
I used =SUM(A1:J1)+COUNTIF(A1:J1,"h")*4+COUNTIF(A1:J1,"hh")*8'
to get a value of 29:
3+1+2+3 = 9, each h is worth 4 (4*4=12) and each hh is worth 8 (1*8=8)
giving 9+12+8 = 29

best wishes

 

[Lars-Åke Aspelin]

Since most Excel functions are case insensitive, I think using H for one
thing and h for another is adding a horrid complication,
How about H and HH ?
With this in A1:J1
h , , h, h, , 3, hh, 1, 2, 3
I used =SUM(A1:J1)+COUNTIF(A1:J1,"h")*4+COUNTIF(A1:J1,"hh")*8'
to get a value of 29:
3+1+2+3 = 9, each h is worth 4 (4*4=12) and each hh is worth 8 (1*8=8)
giving 9+12+8 = 29

best wishes

With the example input, but with H instead of hh, here is an
alternative formula that gives the same result, ie 29:

=SUM(IF(A1:J1="",0,IF(ISNUMBER(A1:J1),A1:J1,CHOOSE(FIND(A1:J1,"hHXxZzY"),4,8,10,20,30,40,50))))

Note: This is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

The XxZzY and ,10,20,30,40,50 is just to indicate that you can add
more letter codes with there corresponding "values" to this example.
So if you have many single letter codes to handle, CHOOSE can handle
254 values, this formula might be a little shorter.

Hope this helps / Lars-Åke