Sum items from a single cell

[FaezMH]

Hi Guys,

I have a problem as below where I intend to sum a range that include certain cell containing 2 items. I can only figure now by using sumproduct that has condition set in. However, when I removed that condition, it all became zero. I tried to include countif into sumproduct but it did not work. Kindly advise.

sumproduct.xlsx
	A	B	C	D	E	F	G
5	Serial No					Total	Expected
6	a) 64B00115, b) 64B00120,	c) 64B00116 d) 64B00121	64B00117	64B00118	64B00119	3	7
Sheet2

Cell Formulas
Range	Formula
F6	F6	=SUMPRODUCT((LEN($A$6:$E$6)-LEN(SUBSTITUTE($A$6:$E$6,"b)","")))/LEN("b)")+(LEN($A$6:$E$6)-LEN(SUBSTITUTE($A$6:$E$6,"c)","")))/LEN("c)")+(LEN($A$6:$E$6)-LEN(SUBSTITUTE($A$6:$E$6,"d)","")))/LEN("d)"))
Press CTRL+SHIFT+ENTER to enter array formulas.

Thanks.
Faez

 

[Legacy 143009]

Not the most elegant solution bu will work:

=FLOOR(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE($A6, $B6, $C6, $D6, $E6, )," ",""), ",", ""), "a", ""), "b", ""), "c", ""), "d", ""), ")", ""))/8, 1)

 

[Fluff]

Maybe

Fluff.xlsm
	A	B	C	D	E	F
5	Serial No					Total
6	a) 64B00115, b) 64B00120,	c) 64B00116 d) 64B00121	64B00117	64B00118	64B00119	7
Main

Cell Formulas
Range	Formula
F6	F6	=ROWS(TEXTSPLIT(TEXTJOIN(CHAR(10),,A6:E6),,CHAR(10)))

 

[FaezMH]

Not the most elegant solution bu will work:

=FLOOR(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE($A6, $B6, $C6, $D6, $E6, )," ",""), ",", ""), "a", ""), "b", ""), "c", ""), "d", ""), ")", ""))/8, 1)

Hi Flashbond,

Thanks for the prompt reply. But somehow there's something missing in the formula.

 

[FaezMH]

Maybe

Fluff.xlsm
	A	B	C	D	E	F
5	Serial No					Total
6	a) 64B00115, b) 64B00120,	c) 64B00116 d) 64B00121	64B00117	64B00118	64B00119	7
Main

Cell Formulas
Range	Formula
F6	F6	=ROWS(TEXTSPLIT(TEXTJOIN(CHAR(10),,A6:E6),,CHAR(10)))

Hi Fluff,

Thanks. This really helps to solve the problem. It did not come to my mind at all that this formula is so powerful.
A lot more for me to learn from you guys and the forum

Thanks again for your help.
Regards,
Faez

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Legacy 143009]

But somehow there's something missing in the formula.

Oh, it is the extra argument after $E6 must be deleted. Nevermind, @Fluff has already come with a beautiful solution.