CountIFS

KevinSue1957

New Member
Joined
Feb 9, 2015
Messages
2
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.
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,550
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

New Member
Joined
Feb 9, 2015
Messages
2
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,626
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 6, 2012
Messages
1,550
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")
 

Watch MrExcel Video

Forum statistics

Threads
1,119,065
Messages
5,575,923
Members
412,689
Latest member
nhsmedic
Top