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

 

[ramez75]

Thank you, I will try it.

Can you tell me how to modify your function to use dynamic ranges. I don't have fixed columns and users add data daily

Thank you

RB

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.

 

[Aladin Akyurek]

Thank you, I will try it.

Can you tell me how to modify your function to use dynamic ranges. I don't have fixed columns and users add data daily

Thank you

RB

In which column do you have Date, Product Line, Problem Type, and Serial? And what is the name of the sheet where the data is located? Moreover, does the data start at row 2?

 

[ramez75]

They are located in sheet3
Date = sheet3 column A
Product Line = sheet3 column B
Problem Type = sheet3 column C
Serial = sheet3 column D
Data is located starting row2

There are 2 sheets, sheet3 (Problem Tracker) is where all the data is recorded and sheet8 (EOL) is where the functions are located that extract data from sheet3

Hope that make sense

Thanks

In which column do you have Date, Product Line, Problem Type, and Serial? And what is the name of the sheet where the data is located? Moreover, does the data start at row 2?

 

[ramez75]

Ok so I made the below work in the vba if anyone want to use it in the future.

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

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

I am going to try the function from Aladin Akyurek

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

 

[ramez75]

Aladin Akyurek,

I used =SUM(IF(FREQUENCY(IF('Sheet3'!A:A=A2267,IF('Sheet3'!B:B=B2267,'Sheet3'!D:D)),'Sheet3'!D:D),1)) but didn't work but I didn't get any error either. I just get 0's. So far only the SUMPRODUCT function that jorismoering provided worked but I had to modify it to avoid blank cells using a VBA. I was hoping for something simpler

erow = Sheet8.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

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

Cells(erow + i, 8) = "=SUMPRODUCT(('Sheet3'!R" & erow1 & "C[-7]:R" & erows & "C[-7]=RC[-7])*('Sheet3'!R" & erow1 & "C[-6]:R" & erows & "C[-6]=RC[-6]),1/COUNTIFS('Sheet3'!R" & erow1 & "C[-7]:R" & erows & "C[-7],'Sheet3'!R" & erow1 & "C[-7]:R" & erows & "C[-7],'Sheet3'!R" & erow1 & "C[-6]:R" & erows & "C[-6],'Sheet3'!R" & erow1 & "C[-6]:R" & erows & "C[-6],'Sheet3'!R" & erow1 & "C[-4]:R" & erows & "C[-4],'Sheet3'!R" & erow1 & "C[-4]:R" & erows & "C[-4]))"

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.