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]

It is actually operating perfectly now! Thank you sooo so much Aladin! You're help has been invaluable. This completely answers my initial question.

If possible, I wanted to ask a couple more questions:
- 1.) Would it be better to name these ranges and use named ranges as opposed to updating the range manually every time I add data?
- 2.) If I wanted to use this to encompass a broader amount of data (ie. take one of the criteria out), would it be as simple as removing IF('Raw Data'!$R$2:$R$60000=$C$3, and then one of the parentheses? Or would this mess up the frequency function?

Again, thank you so much And I am currently working on removing the IF('Raw Data'!$R$2:$R$60000=$C$3, and trying to get a number for non-specific purposes.

 

[Renaissance]

The latest update on the formula you posted works soundly. It is specific to the criteria I need, and when I remove the "IF('Raw Data'!$R$2:$R$60000=$C$3," I am doing so to try and get the information to be less specific, say for example, all Accounts. SO I remove one part and come up with:

=SUM(IF(FREQUENCY(IF('Raw Data'!$D$2:$D$60000<>"",
IF('Raw Data'!$B$2:$B$60000=$D11,
IF('Raw Data'!$S$2:$S$60000=J$4,
MATCH('Raw Data'!$D$2:$D$60000,'Raw Data'!$D$2:$D$60000,0)))),
ROW('Raw Data'!$D$2:$D$60000)-ROW('Raw Data'!$D$2)+1),1))

Only thing is, that when I put this in, I get 0, so I wondered if I misinterpreted how frequency worked. Does it need more criteria to function properly? Or did I simply write this incorrectly? I'm loving that formula by the way, and I am using named ranges now, which seems to similarly to the $R$2:$R$60000.

 

[Aladin Akyurek]

It is actually operating perfectly now! Thank you sooo so much Aladin! You're help has been invaluable. This completely answers my initial question.

You are welcome.

If possible, I wanted to ask a couple more questions:
- 1.) Would it be better to name these ranges and use named ranges as opposed to updating the range manually every time I add data?

Let's try the following recipe.

Define Lrow with Raw Data as Scope by means of Formulas | Name Manager as referring to:

=MATCH(9.99999999999999E+307,'Raw Data'!$D:$D)

Define Drange as referring to:

='Raw Data'!$D$2:INDEX('Raw Data'!$D:$D,'Raw Data'!Lrow)

Brange as:

='Raw Data'!$B$2:INDEX('Raw Data'!$B:$B,'Raw Data'!Lrow)

Srange as:

='Raw Data'!$S$2:INDEX('Raw Data'!$S:$S,'Raw Data'!Lrow)

Rrange as:

='Raw Data'!$R$2:INDEX('Raw Data'!$R:$R,'Raw Data'!Lrow)

Ivec as:

=ROW(Drange)-ROW(INDEX(Drange,1,1))+1

Now we can re-write our earlier formula:

=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))

as follows:

=SUM(IF(FREQUENCY(IF(Drange<>"",
   IF(Brange=$C$3,
   IF(Rrange=$D5,
   IF(Srange=E$4,
     MATCH(Drange,Drange,0))))),
       Ivec),1))

- 2.) If I wanted to use this to encompass a broader amount of data (ie. take one of the criteria out), would it be as simple as removing IF('Raw Data'!$R$2:$R$60000=$C$3, and then one of the parentheses? Or would this mess up the frequency function?

Again, thank you so much And I am currently working on removing the IF('Raw Data'!$R$2:$R$60000=$C$3, and trying to get a number for non-specific purposes.

The latest update on the formula you posted works soundly. It is specific to the criteria I need, and when I remove the "IF('Raw Data'!$R$2:$R$60000=$C$3," I am doing so to try and get the information to be less specific, say for example, all Accounts. SO I remove one part and come up with:

=SUM(IF(FREQUENCY(IF('Raw Data'!$D$2:$D$60000<>"",
IF('Raw Data'!$B$2:$B$60000=$D11,
IF('Raw Data'!$S$2:$S$60000=J$4,
MATCH('Raw Data'!$D$2:$D$60000,'Raw Data'!$D$2:$D$60000,0)))),
ROW('Raw Data'!$D$2:$D$60000)-ROW('Raw Data'!$D$2)+1),1))

Only thing is, that when I put this in, I get 0, so I wondered if I misinterpreted how frequency worked. Does it need more criteria to function properly? Or did I simply write this incorrectly? I'm loving that formula by the way, and I am using named ranges now, which seems to similarly to the $R$2:$R$60000.

Using the original formula, the removal of Rrange and the associated condition go as follows:

=SUM(IF(FREQUENCY(IF(Drange<>"",
   IF(Brange=$C$3,
   IF(Rrange=$D5,
   IF(Srange=E$4,
     MATCH(Drange,Drange,0))))),
       Ivec),1))

Leading to:

=SUM(IF(FREQUENCY(IF(Drange<>"",
   IF(Brange=$C$3,
   IF(Srange=E$4,
     MATCH(Drange,Drange,0)))),
       Ivec),1))

 

[Renaissance]

Hey Aladin,

For some reason, when I use the formula and it only encounters one occurrence that matches all the criteria, it values it at 0. So all of my data that amounts to 1 is automatically zeroed out. Do you know why this might be? Thanks

It's been a little while since I've jumped on the forums (super busy with wife & kids, school and work), but I hope all has been well for you.

 

[Aladin Akyurek]

Hey Aladin,

For some reason, when I use the formula and it only encounters one occurrence that matches all the criteria, it values it at 0. So all of my data that amounts to 1 is automatically zeroed out. Do you know why this might be? Thanks

It's been a little while since I've jumped on the forums (super busy with wife & kids, school and work), but I hope all has been well for you.

Hard to diagnose. Perhaps you can provide a sample that illustrates the problem.

 

[Renaissance]

It has been too long! Time slips through my fingers, but I did end up getting the formulae to work. Thank you so much for answering my questions!!

 

[Aladin Akyurek]

It has been too long! Time slips through my fingers, but I did end up getting the formulae to work. Thank you so much for answering my questions!!

Glad to hear that. A happy new year.