Add a value to a letter

[Nannakl]

Hello,

i am making a spreadsheet for vacation at my work, and need a formular that kan add a value to a letter.

I want to make "H"=1, and "h"=0.5

So that when the letter is typed in a row, it will at last show how many days of vacation the person has taking.
if "H" is typed 4 times, and "h" is typed 4 times, it will at the ens say that the person has used 6 vacation days.

I have tried to use "=SUM(COUNTIF(D7:NE7,{"H","h"})*{1,0.5})

where D7:NE7 is the row that the letters should be typed in.

Although i maybe think that excel can't differentiate lower case and upper case. Do any of you know if it is possible to get excel to do that?


Best regards Nanna

[Phuocam]

Try this ...

=COUNT(FIND("H",D7:NE7))+COUNT(FIND("h",D7:NE7))/2

Enter with Ctrl+Shift+Enter.

[Nannakl]

Try this ...

=COUNT(FIND("H",D7:NE7))+COUNT(FIND("h",D7:NE7))/2

Enter with Ctrl+Shift+Enter.

It doesn't work. Shouldn't it also contain some kind of definition of the letter's value?

[Phuocam]

It worked for me.

[Tony Valko]

It doesn't work.

Should work.

Did you array enter the formula?

[Nannakl]

Sorry, it was me who accidentally marked a wrong cell, that contained another formula. It works perfectly now, thank you!

[Tony Valko]

Good deal. Thanks for the feedback!