Forum Discussion
Solved
Ignore duplicates with multiple criteria and between 2 dates
Hi all I'm doing a training db and need to specify how many learners completed specific, accredited courses between 2 dates that are on a second sheet. I have a list of names in Column A (some d...
=COUNTA( UNIQUE( FILTER('Training DB'!A2:A27, ('Training DB'!B2:B27="Yes")* ('Training DB'!C2:C27<>"Legacy")* ('Training DB'!G2:G27="Completed")* ('Training DB'!F2:F27>=A1)* ('Training DB'!F2:F27<=A2),"" ) ) )
=COUNTIFS('Sheet 1'!J2:J27,">="&'Sheet 2'!A1,'Sheet 1'!J2:J27,"<="&'Sheet 2'!A2,'Sheet 1'!D2:D27,"Yes",'Sheet 1'!L2:L27,"Completed",'Sheet 1'!I2:I27,"<>Legacy")
Is this what you are looking for?
No. That gives me what I can already get - which is great but I also need it to filter out duplicates of names so that each person is only counted once no matter how many times they completed these specific-type courses within the timeframe.
=SUMPRODUCT((COUNTIF('Training DB'!A2:A27,'Training DB'!A2:A27)=1)*('Training DB'!B2:B27="Yes")*('Training DB'!C2:C27<>"Legacy")*('Training DB'!G2:G27="Completed"))