Sum for Numbers extracted from string over rows (no VBA)

[Michael Kensy]

Hola and thanx for reading in advance

I am searching for a solution exclusively via excel functions, not VBA.

In column 'D' of my sheet I merge numbers of several columns and separate them with '/'. Actually it is DurationProgress/DurationRemaining/DurationPlanned ($G$41:$G$258&CHAR(10)&"/"&$H$41:$H$258&CHAR(10)&"/"&$I$41:$I$258)

I already sum these numbers below columns G:I and I want to sum these 3 numbers below the merged column as well but not reference sums from that origin columns (just for double check reason).

I did succeed to extract any single part of this 3 part string but I cant make EXCEL to sum that par over rows. The formula I created '=Sum(MID($D$41:$D$285,1,SEARCH("/",$D41)-2))' returns #Value if just entered as a normal formula and zero if entered as an ARRAY formula. A control formula checking if the result is a number tells 'True' in case of the result 'zero'.

Can someone help me to get what I am doing wrong here?
--
Regards Michael

 

[Peter_SSs]

Re: Help with Function needed: Sum for Numbers extracted from string over rows (no VBA)

Try this array formula. It should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}.

Excel Workbook
	D
41	2/3/5
42
43
44
45
46
47	5/0/62
48
49
50
51
52
53	120/5/36
54
55
56
57
58
59	127/8/103
Check Sum

 

[Michael Kensy]

Re: Help with Function needed: Sum for Numbers extracted from string over rows (no VBA)

Any idea, anyone? Using the IsError function did not really bring me further.

=Sum(If(IsError,,MID($D$41:$D$285,1,SEARCH("/",$D41)-2)))

still delivers either zero as normal formula or the extracted value just of the first cell (D41) as an array formula.
--
Cheers Michael

 

[Peter_SSs]

Re: Help with Function needed: Sum for Numbers extracted from string over rows (no VBA)

That sum should be either be 127/8/103 or just one of these numbers.

Did my suggestion in post #11 not meet this goal, or is my data layout incorrect, oe have you moved on to a different question?

 

[Michael Kensy]

Re: Help with Function needed: Sum for Numbers extracted from string over rows (no VBA)

Hola Peter

there really must be some problem reading this board from China. I couldnt get your post from 27th on 29th (yesterday).
I actually checked from my computer at home, again about 8 hours later from my work place PC and (as another post didnt went through again) posted #12 . I understand it looks as i am quite messy although i am sure i am not that disorganised.

I am sorry for the annoyance and thanx for #11 . I will later try to verify your suggestion.
--
Michael

 

[Michael Kensy]

Re: Help with Function needed: Sum for Numbers extracted from string over rows (no VBA)

Thank you Peter, your suggestion works perfectly and delivers the expected values.

Please allow me to ask three questions because I do not only want the solution but also improve my understanding.
-In your suggested formula references on the data Source are relative. I usually use absolute references, especially when working with those data lists. so do you have a reason I dont see to keep the reference relative?
-For the first part of that formula you '...IF(D41:D58<>""...'. Again, I am asking myself, why not using IsBlank() or IsError()? and finally
-Each of the 3 parts within that formula ends with '+0))'. What does '+0' contribute here?
--
Michael

 

[Peter_SSs]

Re: Help with Function needed: Sum for Numbers extracted from string over rows (no VBA)

-In your suggested formula references on the data Source are relative. I usually use absolute references, especially when working with those data lists. so do you have a reason I dont see to keep the reference relative?

There was no indication in the thread question about copying the formula anywhere. In that case, there was no reason to make the references absolute or relative. From the information given so far, there would be no difference between making the references absolute or relative. The relative references required less typing, so I used that.

-For the first part of that formula you '...IF(D41:D58<>""...'. Again, I am asking myself, why not using IsBlank() or IsError()?

Again, "" required less typing than ISBLANK. ISERROR did not seem relevant.

-Each of the 3 parts within that formula ends with '+0))'. What does '+0' contribute here?

SUM requires numeric values to add. LEFT & MID functions return text values. The +0 coerces the text values, where possible, to numeric values that can then be summed.

 

[Michael Kensy]

Re: Help with Function needed: Sum for Numbers extracted from string over rows (no VBA)

Thanx Peter, for your all inclusive help.