sum with if and multiply together

[ngngc]

Hello,

I want to use a formula to create a statistical result for scrap analysis. I dont believe I can explain my necessity with words here, so please see the attachment that I explained what I need with partial analysis of my original file.

I will be very glad if anyone can help me.

Regards

[PeteABC123]

You need to fix your commas/periods in what appears to be a percentage to multiply by. Not sure what you're trying to do. Could you post an expected result and how it's ultimately achieved?
Pete

[ngngc]

for example;

in the file, for the code "5500020" I have 50 kg scrap. If I divide this amount, I will have;
1980000: 50*21,36% = 10,68 kg (I need to see in L5 10,68+18 = 28,68 kg)
6400200: 50*32,45% = 16,225 kg
1952010: 50*25,12% = 12,56 kg (I need to see in L7 12,56+19 = 31,56 kg)
1980010: 50*21,07% = 10,535 kg


I need a formula to see these results in one cell. Thank you.

[PeteABC123]

ngngc:
Try this in L5 and copy up and down.

=SUM($G$4*E5)+SUMIFS(G$4:G$20,$D$4:$D$20,$K5)

Adjust $G$4 as appropriate. let us know if it works.
Pete

[ngngc]

Thank you for your effort and help, this formula helped me for this example but it seems that I should prepare this example more complicated because as "5500020", we have many codes and with this function I will need to identify each code everytime. please see attached file, this time I hope everything will be clearer to understand.

If possible, everytime I need this formula to multiply columns J and L and sum each time with appropriate conditions.

[FDibbins]

ngngc:
Try this in L5 and copy up and down.

=SUM($G$4*E5)+SUMIFS(G$4:G$20,$D$4:$D$20,$K5)

Adjust $G$4 as appropriate. let us know if it works.
Pete

You dont meed to use SUM when multiplying 2 values/cells together...
=$G$4*E5+SUMIFS(G$4:G$20,$D$4:$D$20,$K5)

[JohnTopley]

Why in your example is 192108 not 142*58.62%+96*59.71%

[ngngc]

Sorry about my mistake, please see the correction below;

=142,00*58,62% = 83,24
=96,00*59,71% = 57,32
=SUMIFS(L$2:L$25;$I$2:$I$25;$Q2) = 42,00

=83,24+57,32+42 = 182,56 kg

[JohnTopley]

I could only get the desired result by using a series of helper cells

Helper 1: Gets the Product Code

=IFERROR(INDEX($H$1:$H$25,SMALL(IF($I$1:$I$25=$Q$2,ROW($A$1:$A$25)-ROW($A$1)+1,""),ROWS($A$1:A1))),"")

Enter with Ctrl+Shift+Enter and drag down

Helper 2: Gets the RSN1 values

=IFERROR(INDEX($L$1:$L$25,MATCH(1,($H$1:$H$25=$R19)*($K$1:$K$25="Product"),0)),"")

Enter with Ctrl+Shift+Enter and drag down

Helper 3: gets the % of ingredients

=IFERROR(INDEX($J$1:$J$25,SMALL(IF($I$1:$I$25=$Q$2,ROW($A$1:$A$25)-ROW($A$1)+1,""),ROWS($A$1:C1))),"")

Enter with Ctrl+Shift+Enter and drag down

Calculations:

Multiply "Helper 2" by "Helper 3"

Add together with SUMIFS calculation

[ngngc]

Hello,

Thank you for your help. Seems that there is no way to do it in one cell, so I also prepared one with more than one cell. Basically, I copied the columns K:O to Q:U and carried out the calculation like below;

***Ensuring that the column in the line of "Product" is empty (to avoid complication) and add these 3 formulas to each cells respectively,

R3 (Ingredient 1) =IF($J3="";"";IF(L2="";"";$J3*L2)) [sorry I dont like to see anything if there is no numbers on the cell so I used IF)
R4 (Ingredient 2) =IF($J4="";"";IF(L2="";"";$J4*L2))
R5 (Ingredient 3) =IF($J5="";"";IF(L2="";"";$J5*L2))

***Then I drag these 4 cells (R2:R5) to the right (R2:U5).
***Then I drag down (R2:U5) until to have (R2:U25), eventually I have finished the calculation of dividing the product into ingredients.

***Later I added a formula to the cell X2 like below;

Total amount of scrap for 192108 =SUMIFS(L$2:L$25;$I$2:$I$25;$W2)+SUMIFS(R$2:R$25;$I$2:$I$25;$W2)
(sum for only 192108) (sum with dividing products)
***Then I drag the cell to right and down (X2:U6)

[JohnTopley]

The attached is your original file: did you mean to post your updated one?

[ngngc]

I have uploaded the wrong file sorry. Please see attached file.

[JohnTopley]

You could change

=IF($J3="","",IF(L2="","",$J3*L2))

to

=IF(OR($J3="",L2=""),"",$J3*L2)

I had worked my way through your solution but thanks for the update.

[JohnTopley]

If your problems is SOLVED, can you please mark thread as SOLVED ("Thread Tools" at top of first post).

Thank you.

[ngngc]

Thank you all for your help and efforts.