Unique Count on Filtered List

davidhall80

Well-known Member
Joined
Jul 8, 2006
Messages
663
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
 

Some videos you may like

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
Joined
Mar 10, 2004
Messages
19,368
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
Joined
Jul 8, 2006
Messages
663
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
Joined
Mar 10, 2004
Messages
19,368
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!
 

Watch MrExcel Video

Forum statistics

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