How to count only unique values following a Countifs statement

[s_j_killeen]

Hi I am really stuck with my formula! Any help is much appreciated

I have created my countifs statement which is working fine and returning a result of 3 - but i want to take this one step further and do a count only of unique values to show that although there are 3 headcount assigned to tasks in this week there are actually only 2 resources doing the work.

How do I add to the formula so that it will do a count only of unique values in column A? I have added a mock up of my data below and my formula so far is also below - this formula returns a value of 3 which counts the 2 values for Sarah and 1 value for Bree. I want it to return a value of 2 as there are only 2 unique resources (Sarah and Bree) doing the work in the week 4th May 2019.

=COUNTIFS(Sheet1!$B:$B,"Yes",Sheet1!$D:$D,"Program Management",Sheet1!$E:$E,"Workplace",Sheet1!$C:$C,">"&TODAY(),Sheet1!F:F,">"&0)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Resource (A)[/TD]
[TD]Direct Report (B)[/TD]
[TD]Contract End Date (C)[/TD]
[TD]Role (D)[/TD]
[TD]Stream (E)[/TD]
[TD]4 May 19 (F)[/TD]
[TD]11 May 19 (G)[/TD]
[TD]18 May 19 (H)[/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[TD]Yes[/TD]
[TD]7/10/19[/TD]
[TD]Program Management[/TD]
[TD]Workplace[/TD]
[TD]2.5[/TD]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[TD]Yes[/TD]
[TD]7/10/19[/TD]
[TD]Program Management[/TD]
[TD]Workplace[/TD]
[TD]1.5[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Bree[/TD]
[TD]Yes[/TD]
[TD]14/1/20[/TD]
[TD]Program Management[/TD]
[TD]Workplace[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Marlon[/TD]
[TD]Yes[/TD]
[TD]30/4/20[/TD]
[TD]Program Management[/TD]
[TD]Workplace[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Hi,

With five conditions ... make sure you do not miss any parenthesis ...

You can test following array formula:

=SUM(IF(FREQUENCY(IF($B$2:$B$10="Yes",IF($D$2:$D$10="Program Management",IF($E$2:$E$10="Workplace",IF($C$2:$C$10>TODAY(),IF($F$2:$F$10>0,IF($A$2:$A$10<>"",MATCH($A$2:$A$10,$A$2:$A$10,0))))))),ROW($A$2:$A$10)-ROW($A$2)+1),1))

Hope this will help