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.

 

[Renaissance]

CSE...

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

You might get a noticeable performance degradation.

It yields 0. Drat! And the performance degradation is minimal. Hmm...Would it change any if I used named ranges instead? Also, does the order change anything? Gosh, I can't believe how difficult this is. I'm sure I'm not the only person trying to utilize multiple criteria, excluding duplicates. Hmmmmm.

Thanks again for all of your help. I know we're very close.

 

[Renaissance]

.

 

[Aladin Akyurek]

It yields 0. Drat! And the performance degradation is minimal. Hmm...Would it change any if I used named ranges instead? Also, does the order change anything? Gosh, I can't believe how difficult this is. I'm sure I'm not the only person trying to utilize multiple criteria, excluding duplicates. Hmmmmm.

Thanks again for all of your help. I know we're very close.

Is the following any different?

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

 

[Renaissance]

That also yields 0.

 

[Aladin Akyurek]

That also yields 0.

I'm going to think that there is no record which meet all conditions. You might want to upload to dropbox and post here a public link to the workbook.

 

[Renaissance]

I'm going to think that there is no record which meet all conditions. You might want to upload to dropbox and post here a public link to the workbook.

https://www.dropbox.com/s/tnkzyhxa03to0f2/Example Mr Excel.xlsx

Alright I hope that helps.

 

[Aladin Akyurek]

https://www.dropbox.com/s/tnkzyhxa03to0f2/Example%20Mr%20Excel.xlsx

Alright I hope that helps.

Ranges and conditions are incorrectly paired...

Control+shift+enter:

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

 

[Renaissance]

That gave me an error, so I added what I thought needed to be added, and now I am getting #N/A with:

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

 

[Aladin Akyurek]

That gave me an error, so I added what I thought needed to be added, and now I am getting #N/A with:

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

I have first reduced the range sizes 2..50652 to 1..12 for testing and tried to revert back to the original size using copy paste. So doing I introduced an unequally sized range in a subexpression, etc., a state of affairs that causes #N/A... Corrected for that, we have:

Control+shift+enter:

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

 

[Aladin Akyurek]

I have first reduced the range sizes 2..50652 to 1..12 for testing and tried to revert back to the original size using copy paste. So doing I introduced an unequally sized range in a subexpression, etc., a state of affairs that causes #N/A... Corrected for that, we have:

Control+shift+enter:

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

Another edit...

Control+shift+enter:

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