text has numerical values

[Guest]

I have a row of 15 cells. In some of these cells there is text. The text
has numerical values such as V=1, M=0.5, and H=0.25. There are usually
multiple combination of these text values and I would like to have a seperate
cell show the SUM total within a range. I tried COUNTA, but that returns a
whole number. I'd like to show a total where if I have V V M M H within a
range, using the values above, in my "total" cell I will have the end result
of 3.25. Any suggestions will be greatly appreciated

 

[Ian]

Assuming you row occupies A1:O1, and assuming each cell contains only one
letter.

=COUNTIF(A1:O1,"V")+(COUNTIF(A1:O1,"M")*0.5)+(COUNTIF(A1:O1,"H")*0.25)

 

[Guest]

Ian,
Thanks for the input. However, the cells may contain more than one letter.
Would I include the additional letters in the formula? Such as AM, PM and 2
HR as shown below?

=COUNTIF(B77,"V")+(COUNTIF(B77,"AM")*0.5)+(COUNTIF(B77,"PM")*0.5)+(COUNTIF(B77,"2 HR")*0.25)+COUNTIF(B4141,"V")+(COUNTIF(B4141,"AM")*0.5)+(COUNTIF(B4141,"PM")*0.5)+(COUNTIF(B4141,"2 HR")*0.25)

Thanks,
TXEagle

 

[Ian]

Sorry, I don't know how to do that. You need to determine whether the letter
appears in the cell first, possibly by using SEARCH or FIND, but these both
result in #VALUE error is the letter isn't found which means you can't
combine it with the IF statement to check for all required letters in one
formula. This would mean using something like:
=SEARCH("M",A1)
and the same for V & H (3 cells in all). So we're talking 3 helper rows for
each original row. You can then do a COUNTIF looking for ">0".

Someone else may be able to suggest a more elegant alternative.

 

[Aladin Akyurek]

Create a-column list,

V,1
M,0.5
H,0.25

say, in A2:B2. Name A2:A4 SymList and B2:B4 ValueList.

Then invoke:

=SUMPRODUCT(SUMIF(SymList,E2:K2,ValueList))

where E2:K2 is a range of interest with cells housing instances of V, M,
and H (along with other items).

 

[Aladin Akyurek]

Should read "... a 2-column list ... in A2:B4."

Create a-column list,

V,1
M,0.5
H,0.25

say, in A2:B2. Name A2:A4 SymList and B2:B4 ValueList.

Then invoke:

=SUMPRODUCT(SUMIF(SymList,E2:K2,ValueList))

where E2:K2 is a range of interest with cells housing instances of V, M,
and H (along with other items).

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.