Range Sum Formula

[Phil Hageman]

In the range C3434, there are text and numberic values.
Typical entries are "X", "5.0ot", "4.0la", and others.

In cell W33 I need to sum the "ot" values. For example:
5.0ot, 2.0ot, and 3.5ot entries would appear as 10.5 in
cell W33. If no "ot" entries, then the value entered in
W33 would be zero, "0.0".

In cell R33 I need to sum the "la" values. Etc...

What would the formula be?

Thanks, Phil

 

[keepitcool]

What's unclear about the name of this NG:
Excel.Programming...

Have you noticed there's another NG called
Excel.Worksheetfunctions


Where do YOU think your question belongs?



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Dana DeLouis]

I think this Array Formula might work also. This assumes your data has a
range name "rng"

=SUM(IF(RIGHT(rng,2)="ot",VALUE(LEFT(rng,LEN(rng)-2)),0))

 

[Phil Hageman]

Dana, Thanks for the formula - it works. Can I go
further with this? What would the formula be for
conditional formatting, to return a true for any of the
range cells? Example, if the user enters 1.5ot in a cell,
I want to format the font and background on the basis of
the "ot" portion of the entry.
Thanks, Phil

 

[Phil Hageman]

Thanks, Tom. It works okay. I went further with this in
Dana's answer. Thanks again.

 

[Phil Hageman]

Thanks for your reply; however, I don't know exactly what
you are saying here. Do you mean this question belongs
elsewhere? I certainly want to do things right in this
newsgroup. Phil

 

[keepitcool]

Phil..

I wasn't being friendly, sorry for that.
(may have to do with a heatwave here in Amsterdam)

Your question belongs in

Microsoft.Public.Excel.Worksheet.Functions

As the name implies this NG is related to (VBA) programming

Never mind.. you got your answer from Tom Ogilvy.


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Tom Ogilvy]

In the conditional format, change "Cell is" to "Formula is" in the dropdown

assume the cell you are formatting is cell A5 (or you have multiple cells
selected and the activecell is A5)

in the textbox to the right of "Formula is", put in

=right(A5,2)="ot"

then click the format button and specify the conditional format that you
want.

 

[Phil Hageman]

Thanks, Tom. Does exactly as needed.

-----Original Message-----
In the conditional format, change "Cell is" to "Formula is" in the dropdown

assume the cell you are formatting is cell A5 (or you have multiple cells
selected and the activecell is A5)

in the textbox to the right of "Formula is", put in

=right(A5,2)="ot"

then click the format button and specify the conditional format that you
want.

--
Regards,
Tom Ogilvy





.