abdelfattah
Well-known Member
- Joined
- May 3, 2019
- Messages
- 1,429
- Office Version
- 2019
- 2010
- Platform
- Windows
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 |