I have a colunm that I want to sum. The problem is that each entry
looks like this:

Total: 80.60
Total: 95.00
Total: 84.75

The SUM() function doesn't work on those. How else can I do it?

Thanks.

Just format the cell with the SUM formula as [hh]:mm


--


Regards,


Peo Sjoblom



"brett" wrote in message
ps.com...
I have a colunm that I want to sum. The problem is that each entry
looks like this:

Total: 80.60
Total: 95.00
Total: 84.75

The SUM() function doesn't work on those. How else can I do it?

Thanks.

Try this ARRAY formula that must be entered using ctrl+shift+enter
=SUM(IF(E2:E10<"",VALUE(MID(E2:E10,8,LEN(E2:E10)-8))))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"brett" wrote in message
ps.com...
I have a colunm that I want to sum. The problem is that each entry
looks like this:

Total: 80.60
Total: 95.00
Total: 84.75

The SUM() function doesn't work on those. How else can I do it?

Thanks.

One option, assuming all of your data contains only 1 space located right
before the number to be summed:

=SUMPRODUCT(--(MID(A1:A3,FIND(" ",A1:A3),99)))

HTH,
Elkar


"brett" wrote:

I have a colunm that I want to sum. The problem is that each entry
looks like this:

Total: 80.60
Total: 95.00
Total: 84.75

The SUM() function doesn't work on those. How else can I do it?

Thanks.

1st offering should have been -7. This also works.
=SUM(IF(LEFT(E2:E10,5)="Total",VALUE(RIGHT(E2:E10, LEN(E2:E10)-7))))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Try this ARRAY formula that must be entered using ctrl+shift+enter
=SUM(IF(E2:E10<"",VALUE(MID(E2:E10,8,LEN(E2:E10)-8))))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"brett" wrote in message
ps.com...
I have a colunm that I want to sum. The problem is that each entry
looks like this:

Total: 80.60
Total: 95.00
Total: 84.75

The SUM() function doesn't work on those. How else can I do it?

Thanks.

I've tried Don and Elkar's suggestions. I keep getting an error that
a value used in the formula is of the wrong data type. The are
currency values by the way Peo.

Don's second formula should work, unless you have other characters in
there that are not visible. Did you get the data from a web site or
HTML source? That is notorious for including the non-breaking space
character (char 160), which obviously you can't see. You can test for
this by =LEN(cell) where cell is any of the cells in your range - you
should get 12 (characters) for the examples you quoted earlier.

Hope this helps.

Pete

On Oct 27, 12:21 am, brett wrote:
I've tried Don and Elkar's suggestions. I keep getting an error that
a value used in the formula is of the wrong data type. The are
currency values by the way Peo.

That would suggest that some of the data you are trying to sum doesn't follow
the same format as your examples. Here's another option that might work:

=SUM(IF(ISNUMBER(--MID(A1:A3,FIND(" ",A1:A3),99)),--MID(A1:A3,FIND("
",A1:A3),99),0))

Note: this is an arrary formula and must be entered with CTRL-SHIFT-ENTER
instead of just Enter. If done properly, the formula should be enclosed in {
}.

If this still doesn't work, then post back with more sample data so we can
get a better picture of what you're working with.

HTH,
Elkar



"brett" wrote:

I've tried Don and Elkar's suggestions. I keep getting an error that
a value used in the formula is of the wrong data type. The are
currency values by the way Peo.

The length is 12 characters using len, unless there is a minus:

Total: -12.95

The spreadsheet is coming from a website but the cells I'm trying to
sum don't seem to have anything in them.

This formula works fine:
=IF(LEFT(I16,5)="Total","1", "2")

These don't:
=SUM(VALUE(RIGHT(I2:I58,LEN(I2:I58)-7)))

=SUM(VALUE(RIGHT(I2:I58,5)))

If I manually type the above values into cells and try Don's formula,
still get the same error.

Is there a way to apply some sort of formatting so it just removes
"Total: "? Wouldn't that be simpler?

Click the Home tab, then Edit and Find and Replace. Then apply
=SUM(). That does it.

Sorry, my answer was actually meant to be posted in another thread


--


Regards,


Peo Sjoblom


"brett" wrote in message
ups.com...
I've tried Don and Elkar's suggestions. I keep getting an error that
a value used in the formula is of the wrong data type. The are
currency values by the way Peo.