Using countifs while excluding duplicates

[Renaissance]

Hey Everyone! I just wanted to start off by saying I've been reading the forums for quite some time now and have been able to find a solution through everyone's examples. Now, it looks like I am at an impasse, though.

I currently have a large quantity of raw data that has many different uses columns A:AB with row 1 as headers, while the rows are in the 50,000+. My situation is that I am pulling the total cost of data through a period & call number, while dividing it by the amount of entries from that same data, minus duplicates.

I've been able to use examples like: =SUM(1/COUNTIF(NAMES,NAMES)) for other sets of data. But this one is a bit more complicated.

So far I have been using: =IFERROR(SUMIFS('Raw Data'!$W:$W,'Raw Data'!$R:$R,$C$3,'Raw Data'!$S:$S,F$4,'Raw Data'!$B:$B,$D5)/COUNTIFS('Raw Data'!$R:$R,$C$3,'Raw Data'!$S:$S,F$4,'Raw Data'!$B:$B,$D5),"-")

What I need to do is replace the /COUNTIFS part (or modify it) to exclude duplicates. In this case they are Purchase Order numbers. I don't need to divide a single cost by 4, if they are all a part of that same cost, if that makes sense.

Thanks for any and all time spent in my dilemma. And if I need to insert an excerpt of my raw data, I can.

 

[Aladin Akyurek]

How do we know what corresponds to Purchase Orders in:

=IFERROR(SUMIFS('Raw Data'!$W:$W,'Raw Data'!$R:$R,$C$3,'Raw Data'!$S:$S,F$4,'Raw Data'!$B:$B,$D5)/COUNTIFS('Raw Data'!$R:$R,$C$3,'Raw Data'!$S:$S,F$4,'Raw Data'!$B:$B,$D5),"-")

etc?

 

[Renaissance]

How do we know what corresponds to Purchase Orders in:

=IFERROR(SUMIFS('Raw Data'!$W:$W,'Raw Data'!$R:$R,$C$3,'Raw Data'!$S:$S,F$4,'Raw Data'!$B:$B,$D5)/COUNTIFS('Raw Data'!$R:$R,$C$3,'Raw Data'!$S:$S,F$4,'Raw Data'!$B:$B,$D5),"-")

etc?

The PO's are in column E. I guess I cannot attach anything? Otherwise, I would as an example. But the PO's are in column E.

 

[Aladin Akyurek]

The PO's are in column E. I guess I cannot attach anything? Otherwise, I would as an example. But the PO's are in column E.

I don't see any reference to column E in the formula. Try rather to word your problem without any reference to a formula.

 

[James Snyder]

Just for clarification, you don't really want to exclude duplicates, just only report the unique purchase order number, correct?

 

[Renaissance]

I don't see any reference to column E in the formula. Try rather to word your problem without any reference to a formula.

I apologize, Aladin. So the old countifs that I have been using is: COUNTIFS('Raw Data'!$R:$R,$C$3,'Raw Data'!$S:$S,F$4,'Raw Data'!$B:$B,$D5)

I want to incorporate a way to exclude duplicates, which could only be done in column E. So, it would look something like COUNTIFS('Raw Data'!$R:$R,$C$3,'Raw Data'!$S:$S,F$4,'Raw Data'!$B:$B,$D5,$E:$E,No duplicates)

I know No duplicates does not work as criteria for $E:$E, but some things I'm reading about are people utilizing a formula such as: =COUNT(1/FREQUENCY(($A$1:$A$98=E1)*($B$1:$B$98=F1)*($C$1:$C$98=G1)
*MATCH($D$1:$D$98,$D$1:$D$98,0),ROW($D$1:$D$98)-ROW(INDEX($D$1:$D$98,1))))-1 If I can incorporate frequency into my formula to remove the duplicate counts, then I would be set. But, unfortunately my brain is sizzling right now, trying to incorporate it into my formula.

 

[Renaissance]

Just for clarification, you don't really want to exclude duplicates, just only report the unique purchase order number, correct?

James, I want to only count unique purchase order numbers, within a period, and with specific COA number. So there are multiple criteria similar to what I am summing together, but if I were to continue to divide by my current method, a purchase order with 4 different parts to it (products a, b, c & d) would take the total cost and divide it by 4, and not 1.

Let's say I have spent $100 for a project that has 4 different products. Each product only cost $25, but there are 4 products. I want to know the cost per project, not per product. So my data will be slightly skewed, depending on how many products a project has. My goal is to make my calculations as accurate as possible, so to eliminate the duplicates would give me my exact number.

Thanks again for wracking your brains with me. I know I'll figure it out eventually, but I'm tired of relying only on myself, haha. I could use a shoulder to lean on in this one.

 

[Renaissance]

[TABLE="class: grid, width: 50, align: center"]
<tbody>[TR]
[TD]N/A
[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]PO[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]Category[/TD]
[TD]Period[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]Each[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ABC 1234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11123[/TD]
[TD]201304[/TD]
[TD][/TD]
[TD][/TD]
[TD]25.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ABC 1234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11123[/TD]
[TD]201304[/TD]
[TD][/TD]
[TD][/TD]
[TD]25.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ABC 2345[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11123[/TD]
[TD]201304[/TD]
[TD][/TD]
[TD][/TD]
[TD]25.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ABC 2345[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11123[/TD]
[TD]201304[/TD]
[TD][/TD]
[TD][/TD]
[TD]25.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ABC 2345[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11123[/TD]
[TD]201304[/TD]
[TD][/TD]
[TD][/TD]
[TD]25.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ABC 2345[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11123[/TD]
[TD]201304[/TD]
[TD][/TD]
[TD][/TD]
[TD]25.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


This is a very basic version of what I'm looking at. Since I cannot attach anything.

 

[Aladin Akyurek]

I apologize, Aladin. So the old countifs that I have been using is: COUNTIFS('Raw Data'!$R:$R,$C$3,'Raw Data'!$S:$S,F$4,'Raw Data'!$B:$B,$D5)

I want to incorporate a way to exclude duplicates, which could only be done in column E. So, it would look something like COUNTIFS('Raw Data'!$R:$R,$C$3,'Raw Data'!$S:$S,F$4,'Raw Data'!$B:$B,$D5,$E:$E,No duplicates)

I know No duplicates does not work as criteria for $E:$E, but some things I'm reading about are people utilizing a formula such as: =COUNT(1/FREQUENCY(($A$1:$A$98=E1)*($B$1:$B$98=F1)*($C$1:$C$98=G1)
*MATCH($D$1:$D$98,$D$1:$D$98,0),ROW($D$1:$D$98)-ROW(INDEX($D$1:$D$98,1))))-1 If I can incorporate frequency into my formula to remove the duplicate counts, then I would be set. But, unfortunately my brain is sizzling right now, trying to incorporate it into my formula.

Try to avoid the whole column references for reasons of efficiency. That said:

The denominator would become (I have to guess from the info the included formula implicates)...

=SUM(IF(FREQUENCY(IF('Raw Data'!$E$2:$E$100<>"",
IF('Raw Data'!$R$2:$R$100=$C$3,IF('Raw Data'!$S$2:$S$100=F$4,
IF('Raw Data'!$B$2:$B$100=$D5,
MATCH('Raw Data'!$E$2:$E$100,'Raw Data'!$E$2:$E$100,0))))),
ROW('Raw Data'!$E$2:$E$100)-ROW('Raw Data'!$E$2)+1),1))

 

[Renaissance]

Try to avoid the whole column references for reasons of efficiency. That said:

The denominator would become (I have to guess from the info the included formula implicates)...

=SUM(IF(FREQUENCY(IF('Raw Data'!$E$2:$E$100<>"",
IF('Raw Data'!$R$2:$R$100=$C$3,IF('Raw Data'!$S$2:$S$100=F$4,
IF('Raw Data'!$B$2:$B$100=$D5,
MATCH('Raw Data'!$E$2:$E$100,'Raw Data'!$E$2:$E$100,0))))),
ROW('Raw Data'!$E$2:$E$100)-ROW('Raw Data'!$E$2)+1),1))

Thank you, Aladin. I'll try that out and see how it does. I've read that whole column references aren't efficient, so I have a question: Would it be more efficient to use named columns, or use references such as $R$2:$R$60000?