Count Distinct and subtotal

[dragonmouse]

I want to count distinct stock numbers J4:J1226
I'm using formula =SUMPRODUCT(1/COUNTIF(J4:J1226,J4:J1226)) and it works pleasantly. Now I want to add one more problem. When I filter out perhaps stock numbers worked by a particular person I want it to return a count distinct subtotal.

I can usually use =SUBTOTAL(3, J4:J1226) and it will return a count all the items in that range but t a "distinct" count. I filter on EMPLOYEE NAME in column A (SMITH). SMITH worked 100 products but only 29 of those products were "unique".

IDEAS?

 

[Eric W]

Try:

=SUM(SIGN(FREQUENCY(IF(SUBTOTAL(103,OFFSET(J4,ROW(J4:J1226)-ROW(J4),0))=1,MATCH(J4:J1226,J4:J1226,0)),ROW(J4:J1226)-ROW(J42)+1)))
confirmed with Control+Shift+Enter.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(J4,ROW(J4:J1226)-ROW(J4),0)),IF(J4:J1226<>"",MATCH(J4:J1226,J4:J1226,0))),ROW(J4:J1226)-ROW(J4)+1),1))

 

[dragonmouse]

Oh MY GOODNES...It WORKS PERFECTLY. I would really love to understand the magic though. Could you explain how you came up with this...I've added it to my formulas list, but I don't understand the magic behind it but it's FABULOUS. Thank you so much.

I really wish I was better at this.

 

[Aladin Akyurek]

Here is an explanation involving unique counting using the FREQUENCY function:

https://www.mrexcel.com/forum/excel-questions/633564-counting-duplicates-using-sum-if-frequency-match-2.html#post3156949

The SUBTOTAL(3,OFFSET(J4,ROW(J4:J1226)-ROW(J4),0)) bit just an additional condition for the MATCH bit.

Hope this helps.

 

[dragonmouse]

I'm going to review that. I love this form so much. I'm so dumb but I look smart at work

Thank you so much. This is better than any class. I've taken classes, they just don't go far enough.

 

[Aladin Akyurek]

I'm going to review that. I love this form so much. I'm so dumb but I look smart at work

Thank you so much. This is better than any class. I've taken classes, they just don't go far enough.

You are welcome.