Unique Count on Filtered List

[davidhall80]

I need to get a count of unique Employee IDs, The only problem is I can't use the formula below that I've been using because there is an active filter on column B. Is there a way to get a unique count on a filtered list.


SUM(IF(FREQUENCY(A2:A100,A2:A100)>0,1))



Thanks in advance

 

[Domenic]

Try...

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1)),A2:A100),IF(SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1)),A2:A100)),1))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[davidhall80]

The formula below is how I come up with the 5500 employee transactions. It gives me 5500 out of about 7500. Of this 5500, I need to get a unique count of Employee ID's so I can get an accurate headcount. I need a way to stick your formula at the end of this. The Employee ID's are on the same "New Ending 09-24-06" tab in column B as the other information that I'm pulling from. Is this possible?


SUMPRODUCT(--('New Ending 09-24-06 WO AV'!$Y$2:$Y$7594>0),--('New Ending 09-24-06 WO AV'!$Z$2:$Z$7594>0),--('New Ending 09-24-06 WO AV'!AE2:AE7594>0))

 

[Domenic]

First define the following...

Insert > Name > Define

Name: Array

Refers to:

=SUBTOTAL(3,OFFSET(CustIDRange,ROW(CustIDRange)-MIN(ROW(CustIDRange)),0,1))

Click Ok

Then try...

=SUM(IF(FREQUENCY(IF(RangeX>0,IF(RangeY>0,IF(RangeZ>0,IF(Array,CustIDRange)))),IF(RangeX>0,IF(RangeY>0,IF(RangeZ>0,IF(Array,CustIDRange))))),1))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[davidhall80]

Thanks