Count unique items in filtered list if meets a specific condition

[resourceguru]

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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Col A[/TD]
[TD]Col B[/TD]
[TD]Col C[/TD]
[TD]Col D[/TD]
[/TR]
[TR]
[TD]Row 1[/TD]
[TD]Project[/TD]
[TD]Name[/TD]
[TD]Role[/TD]
[TD]Employee Status[/TD]
[/TR]
[TR]
[TD]Row 2[/TD]
[TD]A[/TD]
[TD]Bob[/TD]
[TD]Designer[/TD]
[TD]FullTime[/TD]
[/TR]
[TR]
[TD]Row 3[/TD]
[TD]B[/TD]
[TD]Ron[/TD]
[TD]Engineer[/TD]
[TD]PartTime[/TD]
[/TR]
[TR]
[TD]Row 4[/TD]
[TD]C[/TD]
[TD]Bob[/TD]
[TD]Designer[/TD]
[TD]FullTime[/TD]
[/TR]
[TR]
[TD]Row 5[/TD]
[TD]D[/TD]
[TD]Alex[/TD]
[TD]PM[/TD]
[TD]FullTime[/TD]
[/TR]
</tbody>[/TABLE]


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.

 

[James006]

Hello,

You could test the following :

=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]

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]

You are welcome ...