CountIFS

[KevinSue1957]

Hi, I hope somebody can help me please. I'm trying to create a dashboard and count using COUNTIFS but I don't know how to stop duplicates. For example I have a sheet of people and projects but I want to count only 1 project although I have 2 leads on the project. I have tried using =COUNTIFS('Overall Data'!N:N,"Y",'Overall Data'!K:K,">31/03/2020",'Overall Data'!L:L,"<31/03/2021") but because more than one person is on the project and we have to put the number of the project against anybody on it, it keeps adding up the list of people on the project. I only want it to count one project to many people on it dependant on the criteria. Thanks.

 

[mrshl9898]

Like this?

=IF( COUNTIFS('Overall Data'!N:N,"Y",'Overall Data'!K:K,">31/03/2020",'Overall Data'!L:L,"<31/03/2021") )>1,1, COUNTIFS('Overall Data'!N:N,"Y",'Overall Data'!K:K,">31/03/2020",'Overall Data'!L:L,"<31/03/2021") )

 

[KevinSue1957]

Like this?

=IF( COUNTIFS('Overall Data'!N:N,"Y",'Overall Data'!K:K,">31/03/2020",'Overall Data'!L:L,"<31/03/2021") )>1,1, COUNTIFS('Overall Data'!N:N,"Y",'Overall Data'!K:K,">31/03/2020",'Overall Data'!L:L,"<31/03/2021") )

Thank you for the help but when I enter the formula I keep getting an error. I get a red bracket after COUNTIFS and after the final date before )>1,1

 

[jasonb75]

You will not be able to do what you want with full columns, you will need to set the formula to a 'realistic' range.

=SUM(IF(FREQUENCY(IF('Overall Data'!N2:N200="Y",IF('Overall Data'!K2:K200>DATE(2020,3,31),IF('Overall Data'!L2:L200<=DATE(2021,3,31),ROW('Overall Data'!N2:N200)))),ROW('Overall Data'!N2:N200))>0,1))

Note that this formula must be array confirmed with Ctrl Shift Enter.

 

[mrshl9898]

Thank you for the help but when I enter the formula I keep getting an error. I get a red bracket after COUNTIFS and after the final date before )>1,1

Sorry,
=IF(COUNTIFS('Overall Data'!N:N,"Y",'Overall Data'!K:K,">31/03/2020",'Overall Data'!L:L,"<31/03/2021")>1,1, COUNTIFS('Overall Data'!N:N,"Y",'Overall Data'!K:K,">31/03/2020",'Overall Data'!L:L,"<31/03/2021")