Count Unique actions based on selection criteria

[delboy2405]

Hi,

I've Had a good search on this forum around this, I can see some things like it but not exactly what I'm after and I'm stuck

I have a table below and I'm trying to count how many Unique values based on number of days

I'm looking to COUNT UNIQUE Ref that have CHANGE DAYS between between 30 and 90 days and Change status is CLOSED

Example answer would be

ABC_064 is Closed and has taken 77 days to Close so I would get 1.

I'm using SUMIFS to count the action days as it doesnt need to be unique the example code is

=SUMIFS(Table1[[count]:[count]], Table1[[Actions Days]:[Actions Days]], ">29",Table1[[Actions Days]:[Actions Days]], "<90", Table1[[Action Status]:[Action Status]], "Closed")

I suspect I need to use SUMPRODUCT but can't get my head round it.

Hopefully all makes sense. Any help appreciated.

Ref	Change Ref	Date	Owner	Action closed Date	Action Status	Change Status	Change Closed Date	Action Days	Change Days	Counts
ABC_064	12345	14/08/2018	Mr White	30/10/2018	Closed	Closed	30/10/2018	77	77	1
ABC_064	12345	14/08/2018	Mr White	01/11/2018	Closed	Closed	30/11/2018	79	77	1
ABC_065	6789	14/08/2018	Mr Black	01/11/2018	Closed	Open		79	128	1
ABC_065	6789	14/08/2018	Mr Black		Open	Open		128	128	1
ABC_065	6789	14/08/2018	Mr Black		Open	Open		128	128	1
ABC_067	18119	14/08/2018	Mr Pink	01/11/2018	Closed	Open		39	88	1
ABC_067	18119	14/08/2018	Mr Pink	01/11/2018	Closed	Open		39	88	1
ABC_067	18119	14/08/2018	Mr Pink		Closed	Open		88	88	1

<tbody>
</tbody>

 

[Special-K99]

Try

=SUMPRODUCT((A$2:A$9<>"")/COUNTIF(A$2:A$9,A$2:A$9&"")*(G2:G9="Closed")*(J2:J9>=30)*(J2:J9<=90))

This part isolates unique references

(A$2:A$9<>"")/COUNTIF(A$2:A$9,A$2:A$9&"")

Dont ask me how it works, I found it on the net ages ago

 

[Eric W]

Try:

=SUM(SIGN(FREQUENCY(IF(Table1[Action Days]>29,IF(Table1[Action Days]<90,IF(Table1[Action Status]="Closed",MATCH(Table1[Ref],Table1[Ref],0)))),ROW(Table1[Ref])-ROW(INDEX(Table1[Ref],1))+1)))

and confirm the formula by pressing Control+Shift+Enter, not just Enter.

 

[delboy2405]

Thanks i’ll Try both soon as I can, no longer at work

 

[delboy2405]

Hi Both,

Both work great, I prefer the SUMPRODUCT method, as I can personally make it work better to add more criteria if required