Translating a letter to a number within an array

[Echoball]

I am able to count the values followed by a comma in each cell within a given
range: C7(3,6,23) E7(5,8-h,10) N7(12,13,23,25-H) The below formula will
present a total count of 10.

=SUM(IF(LEN(TRIM(C7:N7))=0,0,LEN(TRIM(C7:N7))-LEN(SUBSTITUTE(C7:N7,",",""))+1))

These values happen to represent the days of the month. Each cell is under a
month. For example, C7 can be under January and N7 can be July, etc.

The "-h" stands for 1/2 day. For example, N7 shows that someone took July 25
as a 1/2day. How can I incorporate the above formula to recognize that the
"-h" represents 1/2 day not a full day as its calculating now.

I've never written formula's before and just performing the above was
challenging but worthwhile. Can anyone help? Many thanks.

 

[T. Valko]

I'm guessing that you want to subtract 0.5 for each instance of "h" ?

Add this to the end of your formula:

-(SUM(LEN(C7:N7)-LEN(SUBSTITUTE(UPPER(C7:N7),"H","")))/2)

On a side note...

You see how complicated your formula has become? This is due to the way your
data is entered. I would highly recommend you use a different setup where
you enter a single number in a single cell and use an additional single cell
to enter the letter code. Then this would be much easier!