Summing columns that contain letters
 

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.

Summing columns that contain letters
 

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.

Summing columns that contain letters
 

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.

Summing columns that contain letters
 

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.

Summing columns that contain letters
 

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.

Summing columns that contain letters
 

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.

Summing columns that contain letters
 

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.

Summing columns that contain letters
 

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.

Summing columns that contain letters
 

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.

Summing columns that contain letters
 

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

Summing columns that contain letters
 

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

Summing columns that contain letters
 

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.