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, SUMPRODUCT() does not require a range. You can use it with structured table references as well, which is the most flexible (i.e., if you use the table column header, the SUMPRODUCT() will adjust to the number of rows in the table).

If you must use a range, just set the range a little larger than you'll ever need it. So if you think your sheet with the highest number of rows might get as high as 400, use a range like C1:C500 in every instance of that SUMPRODUCT() formula throughout your sheets.

 

[Legacy 143009]

If you must use a range, just set the range a little larger than you'll ever need it. So if you think your sheet with the highest number of rows might get as high as 400, use a range like C1:C500 in every instance of that SUMPRODUCT() formula throughout your sheets.

Somehow C1:C500 is not working for my case. It throws some #VALUE error. Yet, I can check it if you send me a working sample.

 

[inactiveuserps07]

@Flashbond, my statement about SUMPRODUCT() was a general one. I don't know the intricacies of your case, what you're trying to do with the formula, whether it's an appropriate or effective formula for the task, etc. If you'd like to post data here, I'm sure someone will be able to help.

 

[Legacy 143009]

@ErikTyler

I think I figured it out what's going on. If I extend the range for COUNTIF like (B2:B6,B2:B6), it returns an array including 0s for null cells. So it becomes 1/{(3\3\3\1\0)} and can't devide 1 to 0. Eventually I got a #DIV0 error. Any workaround you'd suggest?

Thanks

 

[Legacy 143009]

Ok I handled it like this:

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

Ctr+Shift+Enter

Case Closed!