Summing columns that contain letters

  • Thread starter Thread starter brett
  • Start date Start date
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.
 
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))))
 
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
 
1st offering should have been -7. This also works.
=SUM(IF(LEFT(E2:E10,5)="Total",VALUE(RIGHT(E2:E10,LEN(E2:E10)-7))))
 
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
 
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
 
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?
 
Sorry, my answer was actually meant to be posted in another thread


--


Regards,


Peo Sjoblom
 
Back
Top