need function to read letters as numbers

[PalinoooSVK]

I need help with creating function or giving me advice how to do it easy way. What I need is - there is a table with employers attendance in work, so when someone is in work I put there the number of hours they are in work - it will sub it to monthly result which is working. What I need is when someone is not at work from whatever reason I write some of the abbreviations Im using and I need excel to read those abbreviations as numbers I can preset. For example: if I write "D" I want excel to read it as 11,5 and then sub it. Im using 7 different abbreviations with different numbers. At last I have 3 different monthly results so if I write only number the result (sub of those no) would go to all 3 results which is working just fine, but if i write any of those abbreviations I want excel to sub it only to 2 of those monthly results. Can someone, please, help me with this?

I've tried to make my own function but it doesn't work nor using COLUMN(INDIRECT(.....))

Thanks in advance.

[alansidman]

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

[bebo021999]

Assum D=11.5; E=10; F=5;...in "range"
Something like this:
=COUNTIF("range","D")*11.5+COUNTIF("range","E")*10+COUNTIF("range","F")*5

or, shorter:

=SUMPRODUCT(COUNTIF("range",{"D","E","F"})*{11.5,10,5})

[PalinoooSVK]

This is how it looks, so when I write any number it should sub it to all 3 results (overall, overtime, payment).
When I write any of abbreviations, which values are there, I need it to sub results only in overall and overtime not payment.
Every hour I write there are payed directly but every time someone is not in work, they have to be somewhere (holiday, doctor etc) but that time is not payed directly by me so it wont go to payment,
only to overall time and overtime.

[bebo021999]

Are you using Ex 97-2003 ?

[PalinoooSVK]

yes, here in work we are using this old version

[PalinoooSVK]

Also, back to those abbreviations, if there are any i need it to sum it to those two results with all numbers I write not only abbreviations

[PalinoooSVK]

Assum D=11.5; E=10; F=5;...in "range"
Something like this:
=COUNTIF("range","D")*11.5+COUNTIF("range","E")*10+COUNTIF("range","F")*5

or, shorter:

=SUMPRODUCT(COUNTIF("range",{"D","E","F"})*{11.5,10,5})

I've tried this:
=SUMPRODUCT(COUNTIF("C4:AG4",{"D","PN","OČ","RP","P","PP","ŠK"})*{11.5,7.5,7.5,8,11.5,7.5,7.5})
but it doesn't work, it says that there is a mistake

[phatdear]

Please try this,

Please Login or Register  to view this content.

[bebo021999]

I've tried this:
=SUMPRODUCT(COUNTIF("C4:AG4",{"D","PN","OČ","RP","P","PP","ŠK"})*{11.5,7.5,7.5,8,11.5,7.5,7.5})
but it doesn't work, it says that there is a mistake

Try to replace "C4:AG4" (string) by C4:AG4 (reference)