SUM If Adjacent Values Are Unique

[Legacy 143009]

Hi,

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]PRODUCTS[/TD]
[TD] LOT NO.[/TD]
[TD]ERROR TYPE[/TD]
[TD]ERROR COUNT[/TD]
[TD]TOTAL LOT QT.[/TD]
[/TR]
[TR]
[TD]PRODUCT1[/TD]
[TD]1[/TD]
[TD]ERROR1[/TD]
[TD]2[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]PRODUCT1[/TD]
[TD]1[/TD]
[TD]ERROR2[/TD]
[TD]3[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]PRODUCT1[/TD]
[TD]1[/TD]
[TD]ERROR3[/TD]
[TD]5[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]PRODUCT1[/TD]
[TD]2[/TD]
[TD]ERROR1[/TD]
[TD]2[/TD]
[TD]500[/TD]
[/TR]
</tbody>[/TABLE]

I have a similar table to follow production errors. To be able to create a graph later, I don't use a Pivot Table to report this data.

So have another table to show error percentages. One sample cell is given below to represent it:

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]PRODUCTS[/TD]
[TD]ERROR1[/TD]
[TD]ERROR2[/TD]
[TD]ERROR3[/TD]
[/TR]
[TR]
[TD]PRODUCT1[/TD]
[TD]=SUMIFS(ERROR COUNT, PRODUCTS, THIS PRODUCT, ERROR TYPE, THIS ERROR)/SUMIF(PRODUCTS, THIS PRODUCT, TOTAL LOT QT)*100[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PRODUCT2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PRODUCT3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PRODUCT4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

..and same for the other products and errors.

For ERROR1, the formula above will calculate "4/1100*100" but this is not I want. In fact, the first 3 record line belong to the same LOT NUMBER. So 200 is the same quantity for different ERROR records.

Last of all, the right calculation, for intance speaking about ERROR1, should be "4/700*100". Total amount of product entered to my depot is totally 700 actually.

Now, somewhere in the denominator section I have to say "Sum if LOT Numbers are distinctive".

Waiting for your helps. Have a nice day fellas!

 

[inactiveuserps07]

@Flashbond, given your syntax, it looks to me like you'd want this:

=SUMIFS(ERROR COUNT, PRODUCTS, THIS PRODUCT, ERROR TYPE, THIS ERROR)/SUMIFS(TOTAL LOT QT, PRODUCTS, THIS PRODUCT, ERROR TYPE, THIS ERROR)*100

 

[Legacy 143009]

Nope, let's assume the last line of the first table is ERROR2. My formula will calculate "2/1100*100". Your formula will calculate "2/200*100". What I want is "2/700*100".

 

[inactiveuserps07]

Yes, I see. Try this:

=SUMIFS(ERROR COUNT, PRODUCTS, THIS PRODUCT, ERROR TYPE, THIS ERROR)/SUMPRODUCT((PRODUCTS=THIS PRODUCT)*(1/COUNTIF(LOT NO., LOT NO.))*(TOTAL LOT QT.))*100

 

[Legacy 143009]

Yes, I am not with my PC but this might seem to be work. I know the right fonction is sumproduct but I don't know exactly how to use it. I'll try ASAP. Is this an array function?

 

[admiral100]

Hi,

Is that what you were looking for?

=SUMPRODUCT(--($C$2:$C$5&B2=C2&B2),$D$2:$D$5)/SUMPRODUCT(--($C$2:$C$5&B2=C2&B2),$E$2:$E$5)*100

 

[inactiveuserps07]

@Flashbond, the formula I last suggested is an array formula, but because it's wrapped in SUMPRODUCT(), you don't need to use Ctrl+Shift+Enter — just Enter.

 

[Legacy 143009]

Yes, I see. Try this:

=SUMIFS(ERROR COUNT, PRODUCTS, THIS PRODUCT, ERROR TYPE, THIS ERROR)/SUMPRODUCT((PRODUCTS=THIS PRODUCT)*(1/COUNTIF(LOT NO., LOT NO.))*(TOTAL LOT QT.))*100

Yes, this is the right formula! Thanks

 

[inactiveuserps07]

@Flashbond, glad it worked out for you, and thanks for reporting back.

 

[Legacy 143009]

I don't want to keep alive this post but I have one question about SUMPRODUCT. It needs a specific range of values like "C1:C64" But I need a solution for general usage because I am keeping mounthly tracks on seperate spread sheets, so my row counts vary. Can anyone provide a solution can be apllied generally? Thanks