Sum numbers from text&num array?

[ljubo_gr]

Hello,
i need help of looking an array of data, please Find {0,1,2,3,4,5,6,7,8,9}, and then Sumproduct.

W50	150	s768	Q737
5564	Y325	X7000	65000
K1500	1200	C3300	2500
1200	e700	b100	1000

<tbody>
</tbody>

Result should be: 91,094 units. Text will mostly be single char letter, always in front, from beginning.
THANKS in advance!

 

[godsaaint]

Someone might be able to give you something more efficient but this should do it:

=SUMPRODUCT(ISTEXT(A1:D4)*MID(A1:D4,2,255))+SUM(A1:D4)

EDIT: nevermind, didn't realize you said mostly, so it can be 2 or more text characters. Go with the vba solution below.

 

[BarryL]

heres a UDF

Function sumranges(ParamArray cellranges() As Variant)
Dim cell As Range, i As Long, area As Variant
For Each area In cellranges
If TypeName(area) = "Range" Then
For Each cell In area
If Asc(UCase(cell)) > 64 And Asc(UCase(cell)) < 91 Then
i = i + (Mid(cell, 2, 256) + 0)
Else
i = i + cell
End If
Next
End If
Next
sumranges = i
End Function

 

[pgc01]

Hi

Similar to godsaaint's solution:

=SUMPRODUCT(0+MID(C2:F5,1+ISTEXT(C2:F5),9))

 

[ljubo_gr]

hi

similar to godsaaint's solution:

=sumproduct(0+mid(c2:f5,1+istext(c2:f5),9))

#value!

 

[BarryL]

#value!

udf work?

 

[ljubo_gr]

Thank you very much BarryL, but prefer formula, I already have dozens of codes inside wbook.

I don't know why this pgc01 doesn't work, could be sum cell is on another sheet?

=SUMPRODUCT(0+{"6000"\""\""\"";""\"1500"\""\"";"31550"\""\""\"";"6000"\""\""\"";"3000"\""\""\"";"11000"\""\""\"";"10000"\""\""\"";"4765"\""\""\"";"6000"\""\""\"";"5970"\""\""\"";"12000"\""\""\"";"6000"\""\""\"";"25000"\""\""\"";"7000"\""\""\"";"2000"\""\""\"";"6975"\""\""\"";"31040"\""\""\"";""\"20000"\""\"";""\"12520"\""\"";""\"32740"\""\"";""\"12010"\""\"";""\"6000"\""\"";""\"2500"\""\"";""\"8000"\""\"";""\"2000"\""\"";""\"19000"\""\"";""\"6000"\""\""})

 

[Marcelo Branco]

See if pgc's formula with a small adjust works

=SUMPRODUCT(0+MID(A1:D4&"0",1+ISTEXT(A1:D4),9))/10

M.

 

[BarryL]

See if pgc's formula with a small adjust works

=SUMPRODUCT(0+MID(A1:D4&"0",1+ISTEXT(A1:D4),9))/10

M.

Oi Marcelo, why add the 0 to divide it off?

OP this works on the sample you posted.

 

[Marcelo Branco]

Oi Marcelo, why add the 0 to divide it off?

OP this works on the sample you posted.

To deal with blank cells.
The formula adds a zero to each cell, so a blank cell becomes "0", but the numbers become greater (multiplied by 10) - that's why we need to divide the final result by 10.

M.