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!

 

[BarryL]

Nice. Thanks for the explanation.

 

[Marcelo Branco]

What i meant with "deal with blank cells" is
0 + "" = #VALUE!
but this works
0+"0" = 0

M.

 

[pgc01]

Hi

Another option, similar to Marcelo's to account for blank cells (not in the original post):

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

 

[ljubo_gr]

Hi

Another option, similar to Marcelo's to account for blank cells (not in the original post):

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

ME DUMB! I appologise for empty cells!

YES YES YES Thank You pgc01, Your Majesty Marcelo Branco Obrigado, BarryL...
Speak good words, think good thought, blessings upon you ALL!
https://www.mrexcel.com/forum/excel...ext-val-column-show-me-array-row-numbers.html

 

[Marcelo Branco]

ME DUMB! I appologise for empty cells!

YES YES YES Thank You pgc01, Your Majesty Marcelo Branco Obrigado, BarryL...

I do not deserve that much. Simply a commoner ....

M.

 

[ljubo_gr]

If I may, one final question: Now, how to Sum those "text" numbers?
=SUMPRODUCT(0+(0&ISTEXT(C2:F5)*ROWS(C2:F5)) right??

 

[ljubo_gr]

I have another problem, i have negative numbers, so that 0& part not working

=SUMPRODUCT({0\0\0\0\0\0\0\31249;0\0\0\0\0\0\0\32499;11000\0\0\0\0\0\0\0;0\0\0\0\0\0\0\31007;0\31005\0\0\0\0\0\0;30603\0\0\0\0\0\0\0;0\0\31004\0\0\0\0\0;32006\0\0\0\0\0\0\0;#VALUE!\#VALUE!\#VALUE!\#VALUE!\0\0\0\0;94846\0\0\0\0\0\0\#VALUE!;0\91791\0\0\0\#VALUE!\#VALUE!\0;21000\0\0\0\0\0\0\0;0\0\0\0\0\0\31503\0;0\0\30806\0\0\0\0\0;31099\0\0\0\0\0\0\0;0\0\0\0\0\30787\0\0;0\30531\0\0\0\0\0\0;32104\0\0\0\0\0\0\0;0\31438\0\0\0\0\0\0;13699\2300\0\0\0\0\0\0;0\0\0\0\0\0\0\30901;0\0\0\0\0\15726\16085\0;0\0\0\29496\0\0\0\0;0\31409\0\0\0\0\0\0;0\31493\0\0\0\0\0\0;0\30791\0\0\0\0\0\0;0\32901\0\0\0\0\0\0;0\33101\0\0\0\0\0\0;0\18816\0\0\0\0\0\13200;25511\0\0\0\0\0\0\6000;26999\0\0\0\0\0\0\5200;3300\90820\0\0\0\#VALUE!\#VALUE!\0;0\55301\0\0\0\0\0\#VALUE!;0\0\40999\6116\#VALUE!\0\0\0})

How to text value: "0-47115" force to result: -47,115 ??

 

[ljubo_gr]

Divide by 10! Thanks for idea Marcelo Branco!

=SUMPRODUCT((0+MID(C2:F5&0;1+ISTEXT(C2:F5);9))/10)

 

[ljubo_gr]

I have problems with formula, please dear MrExcel forum, problem might cause if someone enter value Q737Q or QQ737 or 737Q that, pgc01 post#13, formula will not work Help.

 

[ljubo_gr]

After a while, i have problems, 'cause i have eight types of data inside of array of cells: w737 737w text737 737text text737text 737 -737 ""(empty cells). From array of cells how to get result: 3685 ??
Sumproduct of this array is zero(737-737), cells with text&num is: 3685, TOTAL IS: 3685