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)

Resource (A)	Direct Report (B)	Contract End Date (C)	Role (D)	Stream (E)	4 May 19 (F)	11 May 19 (G)	18 May 19 (H)
Sarah	Yes	7/10/19	Program Management	Workplace	2.5	3	0
Sarah	Yes	7/10/19	Program Management	Workplace	1.5	0	0
Bree	Yes	14/1/20	Program Management	Workplace	5	0	5
Marlon	Yes	30/4/20	Program Management	Workplace	0	5	3

<tbody>
</tbody>

 

[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