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

 

[Michael Kensy]

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

I just found another post of someone using SUMPRODUCT instead of SUM but that one doesnt work for me as well. Nevertheless I found within the Function Dialog that my MID function obviously sometimes returns a '/' instead of a number. Interestingly even the sum function still accumulates to zero.

May be I should tell here that the above given formula given for D41:D258 ($G$41:$G$258&CHAR(10)&"/"&$H$41:$H$258&CHAR(10)&"/"&$I$41:$I$258) does not really look like this. The formula in column D actually reflects grouped values from columns G:I. In reality it is
=Hours_Remaining_PerTeacher&CHAR(10)&"/"&Hours_Progress_PerTeacher&CHAR(10)&"/"&Hours_Pensum_PerTeacher and these 3 Named Formulas look like

...Remaining...=SUMIF($E$41:$E$285,$E41,$G$41:$G$285) etc.

 

[Michael Kensy]

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

hmm, is the solution too obvious or did I describe my problem too bewildering?

 

[Peter_SSs]

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

Welcome to the MrExcel board!

.. or did I describe my problem too bewildering?

I think this is it. And no sample data with expected results.
Have a look at my signature block below for help to post data & formulas that can pretty easily be copied to test with.

This is very much a guess as to what you may have and may want.

Book1
	D	E	F	G	H	I
41	2 /3 /5			2	3	5
42	5 /0 /62			5	0	62
43	120 /5 /36			120	5	36
44	238
Check Sum

Cell Formulas
Range	Formula
D41	=G41&CHAR(10)&"/"&H41&CHAR(10)&"/"&I41
D44	=SUMPRODUCT(--MID(SUBSTITUTE(D41:D43,CHAR(10)&"/",REPT(" ",20)),{1,20,40},20))

 

[Michael Kensy]

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

I replied 4 times already but I assume this board considers Chinese IP adresses as not good enough. If so that should probably be reconsidered.

Anyway, thanks Peter
First for explaining me my oblivious performance and second for creating an example based on my description and trying to solve my problem.
The example is almost accurate except the sum itself. That sum should be either be 127/8/103 or just one of these numbers.
I already tried your solution but got only #Value as a result. Mentioning this doesnt mean that I ask you to find the failure for me. Instead I want to ask about the concept and the difference to my concept.

Do I understand right that the concept of this calculation is based on the fact that EXCEL automatically trims any value surrounded by blanks? If so thats a smart approach although a bit 'leisure'. In that case my original MID(...Search()) construction should work as well if its done right. I wonder if the 20 collected digets may not merge digits of different numbers.
--
Thanx again
Michael

I hope this time my post goes through as I use a VPN

 

[Peter_SSs]

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

Michael

I will send you a Private Message about the issue of posts not appearing.

In relation to your Excel question, I think I need to know more about your data.

1. It sounds a bit like you may have some blank rows in column D? Or perhaps not completely blanks but not all cells having the same number of numbers in them?

2. "I wonder if the 20 collected digets may not merge digits of different numbers." You originally talked about having columns G, H & I concatenated. Does this statement imply there are 20 columns concatenated, or 20 digits from 3 columns, or something else?

3. To help understand the above and your circumstances better, could we see some small sample data (say 5-10 rows) that demonstrate the variety that you have with your actual data, including any blank rows or cells? My signature block below has help on good ways to do that.

4. Could we have the exact formula that is placed in cell D41?


In relation to your question about Excel automatically trimming spaces around values, there are some circumstances where that happens, but it is not a general rule.

 

[Michael Kensy]

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

Hola Peter

In relation to your Excel question, I think I need to know more about your data.

I am working on that but couldn't figure out yet how the favourite tool actually generates any output.

1. It sounds a bit like you may have some blank rows in column D? Or perhaps not completely blanks but not all cells having the same number of numbers in them?

Completely blank shouldnt be possible as the source cells should at least hold a zero. Also the '/ + Chr(10)' should be in column D for every cell in column D. But cells in column D are vertically merged. That cell is unique per teacher butevery teacher has at least 1 but probably n courses=rows

2. "I wonder if the 20 collected digets may not merge digits of different numbers." You originally talked about having columns G, H & I concatenated. Does this statement imply there are 20 columns concatenated, or 20 digits from 3 columns, or something else?

Sorry I didnt make that clear. The 20 digits are related to your formula. If I understand the formula right than it replaces '/ + Chr(10)' with 20 blanks and then accumulates the values within 20 digits from the 1st, 20th and 40th position.

3. To help understand the above and your circumstances better, could we see some small sample data (say 5-10 rows) that demonstrate the variety that you have with your actual data, including any blank rows or cells? My signature block below has help on good ways to do that.

As I said I am working on that.

4. Could we have the exact formula that is placed in cell D41?

I did that yesterday but again the entire post did not show up here. Once at my workplace i will try one more time.
--
Regards Michael

 

[Peter_SSs]

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

I am working on that but couldn't figure out yet how the favourite tool actually generates any output.

Test in the Test Here forum. When you paste in the code generated by the HTML Makers, it looks a bit like garbage but when you submit your post it should render into good screen shot.

Completely blank shouldnt be possible as the source cells should at least hold a zero. Also the '/ + Chr(10)' should be in column D for every cell in column D. But cells in column D are vertically merged.

Merged cells will be a problem and you do have blank cells if you have vertically merged cells. The merged cells are still actually 3 cells, not one. Look at the following example where the formula in E4 is copied down.

Excel Workbook
	D	E
4	data	FALSE
5		TRUE
6		TRUE
7	data	FALSE
8		TRUE
9		TRUE
Merged Cells

Sorry I didnt make that clear. The 20 digits are related to your formula. If I understand the formula right than it replaces '/ + Chr(10)' with 20 blanks and then accumulates the values within 20 digits from the 1st, 20th and 40th position.

Problem with terminology. You are using the word "digits" where you mean "characters". Digits are 0-9 only.

Once at my workplace i will try one more time.

OK, but I may be away from the forum until the middle of next week.

 

[Michael Kensy]

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

Hallo Peter

Thanx for still looking into it and there is no urgency from my side. I actually postpone to understand how to use this tool and therefor I will try myself to figure out why your formula does not work as intended. I assume you mentioned already that the blanks are the problem.

Nevertheless find below the formula in D41 as requested
=z_dyn_TeachingHours_Remaining_PerTeacher&CHAR(10)&"/"&z_dyn_TeachingHours_Progress_PerTeacher&CHAR(10)&"/"&z_dyn_TeachingHours_Pensum_PerTeacher

with
z_dyn_TeachingHours_Remaining_PerTeacher&CHAR(10)= =SUMIF('Semester 01-2017'!$E$41:$E$336,'Semester 01-2017'!$E41,'Semester 01-2017'!$G$41:$G$336)

z_dyn_TeachingHours_Progress_PerTeacher&CHAR(10)= =SUMIF('Semester 01-2017'!$E$41:$E$336,'Semester 01-2017'!$E41,'Semester 01-2017'!$AF$41:$AF$336)

z_dyn_TeachingHours_Pensum_PerTeacher=SUMIF('Semester 01-2017'!$E$41:$E$336,'Semester 01-2017'!$E41,'Semester 01-2017'!$I$41:$I$336)

--
Regards Michael

 

[Michael Kensy]

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

Dear Peter,

please see below if this helps to view my scenario more clear:

Excel Workbook
	D	G	H	I
41	234.6/96.9/331.5	5.1	25.5	25.5
42		0.	76.5	76.5
43		76.5	76.5	76.5
44		76.5	76.5	76.5
45		76.5	76.5	76.5
46
47	72/24/172.5	0.	0	25.5
48		0.	0	51
49		28.	32	32
50		28.	32	32
51		16.	32	32
52
Semester 01-2017

I recently came across a similar issue where an ISERROR formula was used to eliminate the blanks. I will try to do that next.
--
Regards Michael