modified formula by add sumif function

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
977
Office Version
  1. 2010
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
ABCDEFGHI
1CODEBRANDTYPEORIGINQUANTITYITEMSUM
2AA20-W50 208LQ8EU100+100+100AA370
3AA120-W50 12x1LQ8EU101AA1101
4AA220-W50 15x1LQ8EU102+2+1+2+9AA2216
5AA220-W50 208LCASEU100BB2100
6BB220-W50 12x1LCASEU101BB3101
7BB320-W50 15x1LCASEU102CC 102
8CC10-W40 208LQ8EU20CC120
9CC110-W40 208LQ8EU30
10AA10-W40 208LQ8EU50+20
11
12
sheet1
Cell Formulas
RangeFormula
I3,I5:I8I3=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
NameRefers ToCells
GKC=sheet1!$E$2:$E$10I3, I5:I8
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,487
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Maybe something like this

Pasta1
ABCDEFGHI
1CODEBRANDTYPEORIGINQUANTITYITEMSUM
2AA20-W50 208LQ8EU100+100+100AA370
3AA120-W50 12x1LQ8EU101AA1101
4AA220-W50 15x1LQ8EU102+2+1+2+9AA2216
5AA220-W50 208LCASEU100BB2101
6BB220-W50 12x1LCASEU101BB3102
7BB320-W50 15x1LCASEU102CC 20
8CC 10-W40 208LQ8EU20CC130
9CC110-W40 208LQ8EU30
10AA10-W40 208LQ8EU50+20
Plan3
Cell Formulas
RangeFormula
I2:I8I2=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

Well-known Member
Joined
May 3, 2019
Messages
977
Office Version
  1. 2010
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,487
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 
Last edited:

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,487
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
To understand how the formula extracts the numbers try this

Pasta1
EFGHIJ
1QuantityValue1Value2Value3Value4
2100+70+4010070400
Plan1
Cell Formulas
RangeFormula
G2:J2G2=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.
 
Solution

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,487
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,100
Messages
5,622,722
Members
415,923
Latest member
Kam80

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top