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
--
TXEagle in SAT

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

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

--
Ian
--
"TXEagle" wrote in message
...
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
--
TXEagle in SAT

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(B7:P7,"V")+(COUNTIF(B7:P7,"AM")*0.5)+(COU NTIF(B7:P7,"PM")*0.5)+(COUNTIF(B7:P7,"2 HR")*0.25)+COUNTIF(B41:P41,"V")+(COUNTIF(B41:P41," AM")*0.5)+(COUNTIF(B41:P41,"PM")*0.5)+(COUNTIF(B41 :P41,"2 HR")*0.25)

Thanks,
TXEagle
--
TXEagle in SAT


"Ian" wrote:

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

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

--
Ian
--
"TXEagle" wrote in message
...
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
--
TXEagle in SAT

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.

--
Ian
--
"TXEagle" wrote in message
...
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(B7:P7,"V")+(COUNTIF(B7:P7,"AM")*0.5)+(COU NTIF(B7:P7,"PM")*0.5)+(COUNTIF(B7:P7,"2
HR")*0.25)+COUNTIF(B41:P41,"V")+(COUNTIF(B41:P41," AM")*0.5)+(COUNTIF(B41:P41,"PM")*0.5)+(COUNTIF(B41 :P41,"2
HR")*0.25)

Thanks,
TXEagle
--
TXEagle in SAT


"Ian" wrote:

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

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

--
Ian
--
"TXEagle" wrote in message
...
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
--
TXEagle in SAT

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).

TXEagle wrote:
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

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

Aladin Akyurek wrote:
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).

TXEagle wrote:

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

--

[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.