Sum a row where cells contain text and numbers

[dbarnes]

I am trying to "sum" a row with the following data


A1 has 20gb
B1 has 23gb
C1 is blank
D1 is blank
E1 is the sum of the entire row

in the "E1" Cell I have the following formula to calculate /w text the sum for the row
=SUM(VALUE(LEFT(C4:M4,LEN(C4:M4)-2)))

This works fine if I do not have any blank cells in my row; however, if there are blank cells, I get a "#VALUE!" in cell E1.

How can I calculate a row where the cells contain a mix of numbers and text as well as some cells being blank?

Thanks!

 

[Aladin Akyurek]

=SUMPRODUCT((0&SUBSTITUTE(UPPER(C4:M4),"GB",""))+0)

 

[barry houdini]

Is your text always "gb"?

If so perhaps you can just format the cells as

0"gb" and then put 20 in A1, 23 in B1 and just use

=SUM(A1:D1)

 

[meytithveasna]

=SUMPRODUCT(--TRIM(SUBSTITUTE(A1:D1,"gb","")))