# Count unique items in filtered list if meets a specific condition

#### resourceguru

##### New Member
Hello,

I'm trying to automate the calculation of counting the number of employees on my team if they are full time (as opposed to part time). Sometimes, I filter out some employees on my list, and that will impact what is shown on a graph. What's the correct formula do do this to account for the following simplified scenarios?

Simplified Scenario 1: How many Fulltime employees do I have--without counting duplicates: 2 (i.e., Bob and Alex)
Simplified Scenario 2: Add a filter--how many people on the design team do I have if I filter out the list to only show Bob--without counting duplicates: 1 (i.e., Bob)

Table of simplified data below:

 Col A Col B Col C Col D Row 1 Project Name Role Employee Status Row 2 A Bob Designer FullTime Row 3 B Ron Engineer PartTime Row 4 C Bob Designer FullTime Row 5 D Alex PM FullTime

<tbody>
</tbody>

Currently, I can count the unique values (i.e., employees) based on what I filter out using this function:
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(B2,ROW(B2:B5)-ROW(B2),,1)), IF(B2:B5<>"",MATCH("~"&B2:B5,B2:B5&"",0))),ROW(B2:B5)-ROW(B2)+1),1))
and pressing ctrl+shift+enter to do the calculation

The challenge is how to add the criteria of counting them ONLY IF the employee is FullTime (col D). I'm open to using an entirely different formula if it can take into consideration of me filtering out the rows.

Thanks.

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### James006

##### Well-known Member
Hello,

You could test the following :

Code:
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(B2,ROW(B2:B5)-ROW(B2),,1)),IF(D2:D5="FullTime",IF(B2:B5<>"",MATCH("~"&B2:B5,B2:B5&"",0)))),ROW(B2:B5)-ROW(B2)+1),1))

HTH

#### resourceguru

##### New Member
Thanks! I just applied it to a larger dataset and it worked flawlessly. I think I'm also one step closer to understanding the structure of these formulas with your add-on.

#### James006

##### Well-known Member
You are welcome ...

Replies
3
Views
230
Replies
1
Views
242
Replies
7
Views
472
Replies
3
Views
391
Replies
3
Views
149

1,195,662
Messages
6,011,005
Members
441,579
Latest member
satishrazdhan

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back