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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Ref[/TD]
[TD]Change Ref[/TD]
[TD]Date[/TD]
[TD]Owner[/TD]
[TD]Action closed Date[/TD]
[TD]Action Status[/TD]
[TD]Change Status[/TD]
[TD]Change Closed Date[/TD]
[TD]Action Days[/TD]
[TD]Change Days[/TD]
[TD]Counts[/TD]
[/TR]
[TR]
[TD]ABC_064[/TD]
[TD]12345[/TD]
[TD]14/08/2018[/TD]
[TD]Mr White[/TD]
[TD]30/10/2018[/TD]
[TD]Closed[/TD]
[TD]Closed[/TD]
[TD]30/10/2018[/TD]
[TD]77[/TD]
[TD]77[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ABC_064[/TD]
[TD]12345[/TD]
[TD]14/08/2018[/TD]
[TD]Mr White[/TD]
[TD]01/11/2018[/TD]
[TD]Closed[/TD]
[TD]Closed[/TD]
[TD]30/11/2018[/TD]
[TD]79[/TD]
[TD]77[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ABC_065[/TD]
[TD]6789[/TD]
[TD]14/08/2018[/TD]
[TD]Mr Black[/TD]
[TD]01/11/2018[/TD]
[TD]Closed[/TD]
[TD]Open[/TD]
[TD][/TD]
[TD]79[/TD]
[TD]128[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ABC_065[/TD]
[TD]6789[/TD]
[TD]14/08/2018[/TD]
[TD]Mr Black[/TD]
[TD][/TD]
[TD]Open[/TD]
[TD]Open[/TD]
[TD][/TD]
[TD]128[/TD]
[TD]128[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ABC_065[/TD]
[TD]6789[/TD]
[TD]14/08/2018[/TD]
[TD]Mr Black[/TD]
[TD][/TD]
[TD]Open[/TD]
[TD]Open[/TD]
[TD][/TD]
[TD]128[/TD]
[TD]128[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ABC_067[/TD]
[TD]18119[/TD]
[TD]14/08/2018[/TD]
[TD]Mr Pink[/TD]
[TD]01/11/2018[/TD]
[TD]Closed[/TD]
[TD]Open[/TD]
[TD][/TD]
[TD]39[/TD]
[TD]88[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ABC_067[/TD]
[TD]18119[/TD]
[TD]14/08/2018[/TD]
[TD]Mr Pink[/TD]
[TD]01/11/2018[/TD]
[TD]Closed[/TD]
[TD]Open[/TD]
[TD][/TD]
[TD]39[/TD]
[TD]88[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ABC_067[/TD]
[TD]18119[/TD]
[TD]14/08/2018[/TD]
[TD]Mr Pink[/TD]
[TD][/TD]
[TD]Closed[/TD]
[TD]Open[/TD]
[TD][/TD]
[TD]88[/TD]
[TD]88[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[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