modified formula by add sumif function

[abdelfattah]

i have this formula it sum values as what existed in col e but what i want to be any item is repeated should merged by sumif the result in col h,i

PLUSE.xlsm
	A	B	C	D	E	F	G	H	I
1	CODE	BRAND	TYPE	ORIGIN	QUANTITY			ITEM	SUM
2	AA	20-W50 208L	Q8	EU	100+100+100			AA	370
3	AA1	20-W50 12x1L	Q8	EU	101			AA1	101
4	AA2	20-W50 15x1L	Q8	EU	102+2+1+2+9			AA2	216
5	AA2	20-W50 208L	CAS	EU	100			BB2	100
6	BB2	20-W50 12x1L	CAS	EU	101			BB3	101
7	BB3	20-W50 15x1L	CAS	EU	102			CC	102
8	CC	10-W40 208L	Q8	EU	20			CC1	20
9	CC1	10-W40 208L	Q8	EU	30
10	AA	10-W40 208L	Q8	EU	50+20
11
12
sheet1

Cell Formulas
Range	Formula
I3,I5:I8	I3	=SUMPRODUCT(--(0&TRIM(MID(SUBSTITUTE(E3,";",""),ROW($1:$1000),TEXT(FREQUENCY(-ROW($1:$999),-ISERR(-(0&MID(0&SUBSTITUTE(E3,";",""),ROW($1:$999),1)))*ROW($1:$999))-1,"[<]\0")))))

Named Ranges
Name	Refers To	Cells
GKC	=sheet1!$E$2:$E$10	I3, I5:I8

 

[Marcelo Branco]

Maybe something like this

Pasta1
	A	B	C	D	E	F	G	H	I
1	CODE	BRAND	TYPE	ORIGIN	QUANTITY			ITEM	SUM
2	AA	20-W50 208L	Q8	EU	100+100+100			AA	370
3	AA1	20-W50 12x1L	Q8	EU	101			AA1	101
4	AA2	20-W50 15x1L	Q8	EU	102+2+1+2+9			AA2	216
5	AA2	20-W50 208L	CAS	EU	100			BB2	101
6	BB2	20-W50 12x1L	CAS	EU	101			BB3	102
7	BB3	20-W50 15x1L	CAS	EU	102			CC	20
8	CC	10-W40 208L	Q8	EU	20			CC1	30
9	CC1	10-W40 208L	Q8	EU	30
10	AA	10-W40 208L	Q8	EU	50+20
Plan3

Cell Formulas
Range	Formula
I2:I8	I2	=SUM(IF(A$2:A$10=H2,IFERROR(0+TRIM(MID(SUBSTITUTE(E$2:E$10,"+",REPT(" ",100)),(COLUMN($A$1:$Z$1)-1)*100+1,100)),0)))
Press CTRL+SHIFT+ENTER to enter array formulas.

Hope this helps

M.

 

[abdelfattah]

great formula marcelo can you add formula about item in col h and why the formula contain col z this is no data !

 

[Marcelo Branco]

See
IF(A$2:A$10)=H2

COLUMN($A$1:$Z$1) is used to create a horizontal array {1,2,3,....26}
The construct (COLUMN($A$1:$Z$1)-1)*100+1 generates a horizontal array {1,101,201,........}
This array is used as the second argument of MID

Try Formulas > Evaluate formula to see, step by step, what the formula does

M.

 

[abdelfattah]

IF(A$2:A$10)=H2

i don't think this formula is right i put in h2?

 

[Marcelo Branco]

To understand how the formula extracts the numbers try this

Pasta1
	E	F	G	H	I	J
1	Quantity		Value1	Value2	Value3	Value4
2	100+70+40		100	70	40	0
Plan1

Cell Formulas
Range	Formula
G2:J2	G2	=IFERROR(TRIM(0+MID(SUBSTITUTE(E2,"+",REPT(" ",100)),{1,101,201,301},100)),0)
Press CTRL+SHIFT+ENTER to enter array formulas.

Select G2:J2 (gray area); in the formula bar type the formula above
Confirm with Control+Shift+Enter simultaneously

M.

 

[Marcelo Branco]

i don't think this formula is right i put in h2?

Put the formula in I2

M.

 

[abdelfattah]

Put the formula in I2

but this is not formula without = before i did it but gives me error

 

[Marcelo Branco]

Assuming you already have the unique codes in column H, you should enter the formula in post #2 in I2
Confirm with Ctrl+Shift+Enter simultaneously

M.

 

[abdelfattah]

thanks Marcelo for your assistance everything is ok