Sum numbers from text&num array?

ljubo_gr

Board Regular
Joined
Dec 6, 2014
Messages
244
Office Version
  1. 2016
Platform
  1. Windows
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.

W50150s768Q737
5564Y325X700065000
K15001200C33002500
1200e700b1001000

<tbody>
</tbody>


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

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
What i meant with "deal with blank cells" is
0 + "" = #VALUE!
but this works
0+"0" = 0

M.
 
Upvote 0
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)))
 
Upvote 0
Upvote 0
If I may, one final question: Now, how to Sum those "text" numbers?
=SUMPRODUCT(0+(0&ISTEXT(C2:F5)*ROWS(C2:F5)) right??
 
Last edited:
Upvote 0
I have another problem, i have negative numbers, so that 0& part not working :(
Code:
=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 ??
 
Last edited:
Upvote 0
Divide by 10! Thanks for idea Marcelo Branco!
Code:
=SUMPRODUCT((0+MID(C2:F5&0;1+ISTEXT(C2:F5);9))/10)
 
Upvote 0
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.
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top