CODEWITHSUNDEEP
excelexcel-formula
Alejandro Vargas
Alejandro Vargas

Reputation: 13

Turning this formula into an array to add up all numbers

Got a bunch of lines, always first cell is title which is "Notes" and then the cells might be empty or contain the legend "Pallet contains X empty case(s) due to wrong scan procedure" where X is the number I need to extract and goes from 1 to infinity.

I need to extract all those numbers and them up and show result in a cell at other position. So for example for the attached image, it should return 115.

Sample

By using this formula:

=IF(OR(O1="",O1="Notes"),0,MID(O1,17,LEN(O1)-58)\*1)

I am able to extract the number into the cell next it and return 0 if it is blank or the title.

I tried turning that formula into an array, but it simply does not work... Ideas on how to turn this into a single cell formula array?

Upvotes: 1

Views: 58

Answers (1)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27338

Here is one way of doing this :

enter image description here

=SUM(TOCOL(--TEXTSPLIT(TEXTJOIN(" ",1,A1:A25),," "),2))

Or, Using TEXTAFTER() + TEXTBEFORE()

=SUM(--TOCOL(TEXTBEFORE(TEXTAFTER(A1:A25,"contains "), " "),2))

Or, Something like this:

=SUM(--TOCOL(TEXTAFTER(TEXTBEFORE(A1:A25," ",3)," ",-1),2))

Upvotes: 0

Related Questions