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]

I'm still coming up with 0 as an answer, no matter what other criteria I use. I goofed on the column, but I have no problems changing that. I think it would help if I understood the frequency formula a little better. I've adapted it to my sheet and am currently working on solving this dilemma, but you have helped me to get a lot farther and quicker than I would have on my own, and I appreciate that. Is there a way I can post a screenshot or a part of my excel sheet? I'm using 2013.

=SUM(IF(FREQUENCY(IF('Raw Data'!$D$2:$D$60000<>"",
IF('Raw Data'!$R$2:$R$60000=$C$3,IF('Raw Data'!$S$2:$S$60000=R$4,
IF('Raw Data'!$B$2:$B$60000=$D5,
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))

 

[Aladin Akyurek]

I'm still coming up with 0 as an answer, no matter what other criteria I use. I goofed on the column, but I have no problems changing that. I think it would help if I understood the frequency formula a little better. I've adapted it to my sheet and am currently working on solving this dilemma, but you have helped me to get a lot farther and quicker than I would have on my own, and I appreciate that. Is there a way I can post a screenshot or a part of my excel sheet? I'm using 2013.

=SUM(IF(FREQUENCY(IF('Raw Data'!$D$2:$D$60000<>"",
IF('Raw Data'!$R$2:$R$60000=$C$3,IF('Raw Data'!$S$2:$S$60000=R$4,
IF('Raw Data'!$B$2:$B$60000=$D5,
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))

What this formulaboils down to is to compute a distinct count involving the D-range, targeting items where the R-range is equal to C3, S-range to R4, and the B-range to D5.

 

[Renaissance]

What this formulaboils down to is to compute a distinct count involving the D-range, targeting items where the R-range is equal to C3, S-range to R4, and the B-range to D5.

Alright. I'll mess around with a simpler version of it and build back upon it until I get a better feel for it. I'll try to respond later today or tomorrow with my results.

 

[Renaissance]

Do I need to CSE this to make it an array formula? I'm still tweaking this and am coming up with 0's.

 

[Aladin Akyurek]

Do I need to CSE this to make it an array formula? I'm still tweaking this and am coming up with 0's.

Yes, CSE is needed. Perhaps it is easier if you posted a really scaled-down sample along with the desired result.

 

[Renaissance]

Thank you, Aladin. I have been scaling it down and can get a few numbers to show up other than 0 now, but it is still far off from what I expect to find. I'll keep working at it though, because I'm sure somewhere I messed up somewhere.

If you have a moment, I had a quick question. If I were to use two separate formulas and combine them with * would that cause it to be an "and" statement? If so, I could single out where my issue is coming from and get a better handle on what part of the formulae I need to tweak.

 

[Aladin Akyurek]

Thank you, Aladin. I have been scaling it down and can get a few numbers to show up other than 0 now, but it is still far off from what I expect to find. I'll keep working at it though, because I'm sure somewhere I messed up somewhere.

If you have a moment, I had a quick question. If I were to use two separate formulas and combine them with * would that cause it to be an "and" statement? If so, I could single out where my issue is coming from and get a better handle on what part of the formulae I need to tweak.

Would you post the formula you implemented?

 

[Renaissance]

Sorry for taking so long, Aladin. It's been super hectic with wife and kids flying international and I'm just adjusting to the house being empty. Here is what I condensed it to, and I believe it is working, but now I'm slowly adding to it so I can capture data specific to date, etc.

=SUM(IF(FREQUENCY(IF('Raw Data'!$D$2:$D$50652<>"",
IF('Raw Data'!$B$2:$B$50652=$D5,
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]

Alright, when I try to reincorporate the criteria ",IF('Raw Data'!$R$2:$R$50652=$C$3,IF('Raw Data'!$S$2:$S$50652=R$4," I get 0, which I should be getting 11. So, is it the order I have it in? I'm trying to take the formula I just posted and add
"*IF('Raw Data'!$R$2:$R$50652=$C$3,IF('Raw Data'!$S$2:$S$50652=R$4))" but I still come up with 0, and I understand * to be an "and" function.
This is what I just changed it to, but it's yielding 0 as a result...

=SUM(IF(FREQUENCY(IF('Raw Data'!$D$2:$D$50652<>"",
IF('Raw Data'!$B$2:$B$50652=$D5,
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))*
IF('Raw Data'!$R$2:$R$50652=$C$3,IF('Raw Data'!$S$2:$S$50652=R$4,))

And I am still entering it as CSE

 

[Aladin Akyurek]

Alright, when I try to reincorporate the criteria ",IF('Raw Data'!$R$2:$R$50652=$C$3,IF('Raw Data'!$S$2:$S$50652=R$4," I get 0, which I should be getting 11. So, is it the order I have it in? I'm trying to take the formula I just posted and add
"*IF('Raw Data'!$R$2:$R$50652=$C$3,IF('Raw Data'!$S$2:$S$50652=R$4))" but I still come up with 0, and I understand * to be an "and" function.
This is what I just changed it to, but it's yielding 0 as a result...

=SUM(IF(FREQUENCY(IF('Raw Data'!$D$2:$D$50652<>"",
IF('Raw Data'!$B$2:$B$50652=$D5,
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))*
IF('Raw Data'!$R$2:$R$50652=$C$3,IF('Raw Data'!$S$2:$S$50652=R$4,))

And I am still entering it as CSE

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.