# Unique Count on Filtered List

#### davidhall80

##### Well-known Member
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

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### Domenic

##### MrExcel MVP
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

##### Well-known Member
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

##### MrExcel MVP
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!

Thanks

Replies
3
Views
55
Replies
10
Views
162
Replies
3
Views
36
Replies
16
Views
435
Replies
4
Views
86

Threads
1,113,812
Messages
5,544,458
Members
410,613
Latest member
Texman