Function not to count duplicates

[ramez75]

Hi,

I am new to the thread, I researched to see if I can find an answer to my question but didn't find exactly what I want. I have tried the functions countifs, sumifs but again not getting what I want. Hence, I am open to ideas or suggestions on how to handle this.

I am working on this excel sheet to capture inspection data and plot pass versus failure.

So Sheet8 is the Dashboard where it uses different sumifs and countifs to get data from other worksheets. All seems to work fine with the exception of this

In sheet8 column H - I am using currently the below countifs function which I know is not providing me the right number as it counts duplicates
=COUNTIFS('sheet3'!B:B,B2210,'sheet3'!A:A,A2210)
sheet3 column A = Date
sheet3 column B = Product line
sheet3 column C = Problem Type
sheet3 column D = Serial #

my issue is the inspectors find multiple defects sometime on the same unit hence "sheet3 column D" might have the serial # recorded more than once as each time it corresponds to a different failure.

I want the above counifs function to be modified to account for that and not count duplicate serial number or what other function I need to use to be able to get what I need.

Example

Sheet3 Date

Date Product Line Problem Type Serial No.
11/3/2015 Product A Missing Screw 1234
11/3/2015 Product A Missing Label 1234
11/3/2015 Product A Missing Label 1111
11/3/2015 Product B Missing red wire 2145
11/3/2015 Product C Missing red wire 3214

Hence, sheet8 should show the below

Date Product Line # Units Failed
11/3/2015 Product A 1
11/3/2015 Product A 1
11/3/2015 Product B 1
11/3/2015 Product C 1

Hope that make sense

Help is greatly appreciated

Thank you in advance

RB

 

[jorismoerings]

Hi,

Perhaps this helps you in the right direction:

Book1
	A	B	C	D	E	F	G	H
1	Date	Product Line	Problem Type	Serial
2	11-3-2015	Product A	Missing Screw	1234		Product A	11-3-2015	2
3	11-3-2015	Product A	Missing Label	1234		Product B	11-3-2015	1
4	11-3-2015	Product A	Missing Label	1111		Product C	11-3-2015	1
5	11-3-2015	Product B	Missing red wire	2145
6	11-3-2015	Product C	Missing red wire	3214
Sheet1

Cell Formulas
Range	Formula
H2	=SUMPRODUCT(($A$2:$A$6=G2)*($B$2:$B$6=F2),1/COUNTIFS($A$2:$A$6,$A$2:$A$6,$B$2:$B$6,$B$2:$B$6,$D$2:$D$6,$D$2:$D$6))
H3	=SUMPRODUCT(($A$2:$A$6=G3)*($B$2:$B$6=F3),1/COUNTIFS($A$2:$A$6,$A$2:$A$6,$B$2:$B$6,$B$2:$B$6,$D$2:$D$6,$D$2:$D$6))
H4	=SUMPRODUCT(($A$2:$A$6=G4)*($B$2:$B$6=F4),1/COUNTIFS($A$2:$A$6,$A$2:$A$6,$B$2:$B$6,$B$2:$B$6,$D$2:$D$6,$D$2:$D$6))

 

[ramez75]

Thank you jorismoering. Exactly the result I was looking for. I will modify the functions to work in my workbook. I never used such a combination would you tell me though how it works the logic behind it

Thank you

RB

Hi,

Perhaps this helps you in the right direction:

Book1
	A	B	C	D	E	F	G	H
1	Date	Product Line	Problem Type	Serial
2	11-3-2015	Product A	Missing Screw	1234		Product A	11-3-2015	2
3	11-3-2015	Product A	Missing Label	1234		Product B	11-3-2015	1
4	11-3-2015	Product A	Missing Label	1111		Product C	11-3-2015	1
5	11-3-2015	Product B	Missing red wire	2145
6	11-3-2015	Product C	Missing red wire	3214
Sheet1

Cell Formulas
Range	Formula
H2	=SUMPRODUCT(($A$2:$A$6=G2)*($B$2:$B$6=F2),1/COUNTIFS($A$2:$A$6,$A$2:$A$6,$B$2:$B$6,$B$2:$B$6,$D$2:$D$6,$D$2:$D$6))
H3	=SUMPRODUCT(($A$2:$A$6=G3)*($B$2:$B$6=F3),1/COUNTIFS($A$2:$A$6,$A$2:$A$6,$B$2:$B$6,$B$2:$B$6,$D$2:$D$6,$D$2:$D$6))
H4	=SUMPRODUCT(($A$2:$A$6=G4)*($B$2:$B$6=F4),1/COUNTIFS($A$2:$A$6,$A$2:$A$6,$B$2:$B$6,$B$2:$B$6,$D$2:$D$6,$D$2:$D$6))

 

[ramez75]

I tried the below from Sheet8 and it worked perfectly

=SUMPRODUCT(('Sheet4'!A1310:A1328=A2228)*('Sheet4'!B1310:B1328=B2228),1/COUNTIFS('Sheet4'!A1310:A1328,'Sheet4'!A1310:A1328,'Sheet4'!B1310:B1328,'Sheet4'!B1310:B1328,'Sheet4'!D1310:D1328,'Sheet4'!D1310:D1328))

Then I expanded to include all cells within each column using the below and it didn't work.
=SUMPRODUCT(('Sheet4'!A:A=A2228)*('Sheet4'!B:B=B2228),1/COUNTIFS('Sheet4'!A:A,'Sheet4'!A:A,'Sheet4'!B:B,'Sheet4'!B:B,'Sheet4'!D:D,'Sheet4'!D:D))

What am I doing wrong? I would think it should work

Thanks

RB

 

[jorismoerings]

Hi,

Formula breaks down into 4 parts:

1. check and count for the correct date: this part will creates an array which will return {TRUE;TRUE;TRUE;TRUE;TRUE}
2. check and count for the correct product: eg for product A this part will create an array which will return {TRUE;TRUE;TRUE;FALSE;FALSE}
3. When 1 and 2 are multiplied the return will be an array {1;1;1;0;0}
4. The 3rd part of the formula COUNTIFS($A$2:$A$6;$A$2:$A$6;$B$2:$B$6;$B$2:$B$6;$D$2:$D$6;$D$2:$D$6) counts the number of times the serial number of the line occurs in the total table and will return an array showing {2;2;1;1;1}
5. The last part is taking the reciprocal of the array created by the third part and this will return {1/2;1/2;1/1;1/1;1/1}
6. now sumproduct kicks in which combines the 2 arrays {1;1;1;0;0} * {1/2;1/2;1/1;1/1;1/1} resulting in the correct answer.

 

[ramez75]

I know why the second function didn't work. Blank rows I had 2 blank rows 437 and 438 which I deleted but I still have blank rows after 1328 but will get populated with time. So how can I modify the function to ignore blank rows.

I want to be able to use the function across all the column domain, e.g.: A:A, B;B, etc

Thank you in advance

RB

 

[jorismoerings]

Hi,

To my knowledge you can't. The countif part will not work with just a column reference. So only way i know how to make it work is by altering the countif part to reflect the true range of your cells.

 

[ramez75]

I was trying to use the function in a vba. So each time the user click the button on Sheet8 it will update by inserting the function but since I have empty rows. I cant get it to work

 

[ramez75]

Ok so I used the Record Macro feature and recorded the function that works. Starting with row 2 to row 1325 on sheet3 as the function doesn't calculate with blank rows.

Cells(erow + i, 8) = "=SUMPRODUCT(('sheet3'!R[-2226]C[-7]:R[-903]C[-7]=RC[-7])*('sheet3'!R[-2226]C[-6]:R[-903]C[-6]=RC[-6]),1/COUNTIFS('sheet3'!R[-2226]C[-7]:R[-903]C[-7],'sheet3'!R[-2226]C[-7]:R[-903]C[-7],'sheet3'!R[-2226]C[-6]:R[-903]C[-6],'sheet3'!R[-2226]C[-6]:R[-903]C[-6],'sheet3'!R[-2226]C[-4]:R[-903]C[-4],'sheet3'!R[-2226]C[-4]:R[-903]C[-4]))"

Since the User will add more data into sheet3, the above function will not work beyond row 1325. So I created the below

erows = Sheet3.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

How can I use the erows in the above function so everytime the User update Sheet8 the function will adjust to accommodate all non blank rows in sheet3 using the "erows"

Any ideas

RB

 

[Aladin Akyurek]

In G2 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($A$2:$A$6=G2,IF($B$2:$B$6=F2,$D$2:$D$6)),$D$2:$D$6),1))

This should be much faster than SumProduct\CountIfs version and robust against blanks. However, do not reference whole columns for reasons of efficiency. If needed, you can define dynamic named ranges.