Range Sum Formula

  • Thread starter Thread starter Phil Hageman
  • Start date Start date
P

Phil Hageman

In the range C34:P34, 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
 
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 >
 
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))
 
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
 
Thanks, Tom. It works okay. I went further with this in
Dana's answer. Thanks again.
 
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
 
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 >
 
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.
 
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





.
Click to expand...
 
Back
Top