Summing columns that contain letters

B

brett

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

Don Guillett

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

Elkar

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
 
D

Don Guillett

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

brett

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

Pete_UK

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
 
E

Elkar

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
 
B

brett

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

brett

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

Peo Sjoblom

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


--


Regards,


Peo Sjoblom
 
Top