SOLVED

Ignore duplicates with multiple criteria and between 2 dates

%3CLINGO-SUB%20id%3D%22lingo-sub-2957921%22%20slang%3D%22en-US%22%3EIgnore%20duplicates%20with%20multiple%20criteria%20and%20between%202%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2957921%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%3C%2FP%3E%3CP%3EI'm%20doing%20a%20training%20db%20and%20need%20to%20specify%20how%20many%20learners%20completed%20specific%2C%20accredited%20courses%20between%202%20dates%20that%20are%20on%20a%20second%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20list%20of%20names%20in%20Column%20A%20(some%20duplicated)%2C%20Accredited%20(Yes%20or%20No)%20in%20Column%20D%2C%20Funding%20(A.C.T%2C%20Legacy%2C%20Zero%20Cost)%2C%20End%20Date%20in%20Column%20J%2C%20and%20Status%20(Completed%2C%20In%20Progress%2C%20TBC%2C%20etc.)%20in%20Column%20L%20-%20all%20on%20sheet%201%20shown%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20stats%20are%20on%20sheet%202%20including%20the%20between%20dates%20which%20change%20according%20to%20the%20stats%20required.%26nbsp%3B%20Date%201%20in%20cell%20A1%2C%20date%202%20in%20cell%20A2.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20state%20how%20many%20students%20completed%20accredited%20courses%20that%20weren't%20funded%20by%20Legacy%2C%20between%20the%20two%20dates%20so%20that%20the%20figures%20will%20update%20on%20change%20of%20dates.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20help%3F%26nbsp%3B%20Thank%20you%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJules%3C%2FP%3E%3CP%3ESheet%201%3C%2FP%3E%3CTABLE%20width%3D%22664%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22186%22%3EName%3C%2FTD%3E%3CTD%20width%3D%22119%22%3EAccredited%3F%3C%2FTD%3E%3CTD%20width%3D%2294%22%3EFunding%3C%2FTD%3E%3CTD%20width%3D%22111%22%3EEnd%20Date%3C%2FTD%3E%3CTD%20width%3D%22154%22%3EStatus%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ENother%2C%20Anthony%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3CTD%3ELegacy%3C%2FTD%3E%3CTD%3E26%2F05%2F2021%3C%2FTD%3E%3CTD%3ECompleted%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBloggs%2C%20Joe%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3CTD%3ELegacy%3C%2FTD%3E%3CTD%3E26%2F05%2F2021%3C%2FTD%3E%3CTD%3ECompleted%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPerson%2C%20James%3C%2FTD%3E%3CTD%3ENo%3C%2FTD%3E%3CTD%3ELegacy%3C%2FTD%3E%3CTD%3E01%2F07%2F2021%3C%2FTD%3E%3CTD%3ECompleted%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPeartree%2C%20Patty%3C%2FTD%3E%3CTD%3ENo%3C%2FTD%3E%3CTD%3ELegacy%3C%2FTD%3E%3CTD%3E01%2F07%2F2021%3C%2FTD%3E%3CTD%3ECompleted%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ENother%2C%20Anthony%3C%2FTD%3E%3CTD%3ENo%3C%2FTD%3E%3CTD%3ELegacy%3C%2FTD%3E%3CTD%3E01%2F07%2F2021%3C%2FTD%3E%3CTD%3ECompleted%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ELollipop%2C%20Daisy%3C%2FTD%3E%3CTD%3ENo%3C%2FTD%3E%3CTD%3ELegacy%3C%2FTD%3E%3CTD%3E01%2F07%2F2021%3C%2FTD%3E%3CTD%3ECompleted%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDearheart%2C%20A%3C%2FTD%3E%3CTD%3ENo%3C%2FTD%3E%3CTD%3ELegacy%3C%2FTD%3E%3CTD%3E01%2F07%2F2021%3C%2FTD%3E%3CTD%3ECompleted%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EVimes%2C%20Sam%3C%2FTD%3E%3CTD%3ENo%3C%2FTD%3E%3CTD%3ELegacy%3C%2FTD%3E%3CTD%3E01%2F07%2F2021%3C%2FTD%3E%3CTD%3ECompleted%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDoor%2C%20Bill%3C%2FTD%3E%3CTD%3ENo%3C%2FTD%3E%3CTD%3ELegacy%3C%2FTD%3E%3CTD%3E01%2F07%2F2021%3C%2FTD%3E%3CTD%3ECompleted%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPerson%2C%20James%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3CTD%3ELegacy%3C%2FTD%3E%3CTD%3E06%2F07%2F2021%3C%2FTD%3E%3CTD%3ECompleted%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ELollipop%2C%20Daisy%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3CTD%3ELegacy%3C%2FTD%3E%3CTD%3E06%2F07%2F2021%3C%2FTD%3E%3CTD%3ECompleted%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDoor%2C%20Bill%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3CTD%3ELegacy%3C%2FTD%3E%3CTD%3E06%2F07%2F2021%3C%2FTD%3E%3CTD%3ECompleted%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EVimes%2C%20Sam%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3CTD%3ELegacy%3C%2FTD%3E%3CTD%3E13%2F07%2F2021%3C%2FTD%3E%3CTD%3ECompleted%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBloggs%2C%20Joe%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3CTD%3ELegacy%3C%2FTD%3E%3CTD%3E13%2F07%2F2021%3C%2FTD%3E%3CTD%3ECompleted%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPerson%2C%20James%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3CTD%3EA.C.T.%3C%2FTD%3E%3CTD%3E21%2F07%2F2021%3C%2FTD%3E%3CTD%3ECompleted%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EOgg%2C%20Nanny%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3CTD%3EA.C.T.%3C%2FTD%3E%3CTD%3E22%2F07%2F2021%3C%2FTD%3E%3CTD%3ECompleted%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPeartree%2C%20Patty%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3CTD%3ELegacy%3C%2FTD%3E%3CTD%3E28%2F07%2F2021%3C%2FTD%3E%3CTD%3ECompleted%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ENother%2C%20Anthony%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3CTD%3ELegacy%3C%2FTD%3E%3CTD%3E28%2F07%2F2021%3C%2FTD%3E%3CTD%3ECompleted%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ELollipop%2C%20Daisy%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3CTD%3ELegacy%3C%2FTD%3E%3CTD%3E28%2F07%2F2021%3C%2FTD%3E%3CTD%3EFailed%20Course%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDearheart%2C%20A%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3CTD%3ELegacy%3C%2FTD%3E%3CTD%3E28%2F07%2F2021%3C%2FTD%3E%3CTD%3ECompleted%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEvans%2C%20Christopher%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3CTD%3EZero%20Cost%3C%2FTD%3E%3CTD%3E29%2F07%2F2021%3C%2FTD%3E%3CTD%3EWaiting%20to%20Start%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPeartree%2C%20Patty%3C%2FTD%3E%3CTD%3ENo%3C%2FTD%3E%3CTD%3EA.C.T.%3C%2FTD%3E%3CTD%3E23%2F08%2F2021%3C%2FTD%3E%3CTD%3EIn%20Progress%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPeartree%2C%20Patty%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3CTD%3EZero%20Cost%3C%2FTD%3E%3CTD%3E30%2F09%2F2021%3C%2FTD%3E%3CTD%3ECompleted%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBaird%2C%20Logie%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3CTD%3EA.C.T.%3C%2FTD%3E%3CTD%3E30%2F09%2F2021%3C%2FTD%3E%3CTD%3EIn%20Progress%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ENother%2C%20Anthony%3C%2FTD%3E%3CTD%3ENo%3C%2FTD%3E%3CTD%3EA.C.T.%3C%2FTD%3E%3CTD%3E05%2F10%2F2021%3C%2FTD%3E%3CTD%3ECompleted%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ENother%2C%20Anthony%3C%2FTD%3E%3CTD%3ENo%3C%2FTD%3E%3CTD%3EA.C.T.%3C%2FTD%3E%3CTD%3E11%2F10%2F2021%3C%2FTD%3E%3CTD%3ECompleted%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2957921%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2957998%22%20slang%3D%22en-US%22%3ERe%3A%20Ignore%20duplicates%20with%20multiple%20criteria%20and%20between%202%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2957998%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1216267%22%20target%3D%22_blank%22%3E%40Jules_Rapley%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCOUNTIFS('Sheet%201'!J2%3AJ27%2C%22%26gt%3B%3D%22%26amp%3B'Sheet%202'!A1%2C'Sheet%201'!J2%3AJ27%2C%22%26lt%3B%3D%22%26amp%3B'Sheet%202'!A2%2C'Sheet%201'!D2%3AD27%2C%22Yes%22%2C'Sheet%201'!L2%3AL27%2C%22Completed%22%2C'Sheet%201'!I2%3AI27%2C%22%26lt%3B%26gt%3BLegacy%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20what%20you%20are%20looking%20for%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2958027%22%20slang%3D%22en-US%22%3ERe%3A%20Ignore%20duplicates%20with%20multiple%20criteria%20and%20between%202%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2958027%22%20slang%3D%22en-US%22%3EIf%20you%20can%20post%20a%20file%20it%20would%20be%20great%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2958105%22%20slang%3D%22en-US%22%3ERe%3A%20Ignore%20duplicates%20with%20multiple%20criteria%20and%20between%202%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2958105%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1174419%22%20target%3D%22_blank%22%3E%40Quadruple_Pawn%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo.%26nbsp%3B%20That%20gives%20me%20what%20I%20can%20already%20get%20-%20which%20is%20great%20but%20I%20also%20need%20it%20to%20filter%20out%20duplicates%20of%20names%20so%20that%20each%20person%20is%20only%20counted%20once%20no%20matter%20how%20many%20times%20they%20completed%20these%20specific-type%20courses%20within%20the%20timeframe.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2958172%22%20slang%3D%22en-US%22%3ERe%3A%20Ignore%20duplicates%20with%20multiple%20criteria%20and%20between%202%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2958172%22%20slang%3D%22en-US%22%3EHave%20now%20done%20so.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2958300%22%20slang%3D%22en-US%22%3ERe%3A%20Ignore%20duplicates%20with%20multiple%20criteria%20and%20between%202%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2958300%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1216267%22%20target%3D%22_blank%22%3E%40Jules_Rapley%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DCOUNTA(%0A%20%20%20%20%20%20%20UNIQUE(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20FILTER('Training%20DB'!A2%3AA27%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20('Training%20DB'!B2%3AB27%3D%22Yes%22)*%0A%20%20%20%20%20%20%20%20%20%20%20%20%20('Training%20DB'!C2%3AC27%26lt%3B%26gt%3B%22Legacy%22)*%0A%20%20%20%20%20%20%20%20%20%20%20%20%20('Training%20DB'!G2%3AG27%3D%22Completed%22)*%0A%20%20%20%20%20%20%20%20%20%20%20%20%20('Training%20DB'!F2%3AF27%26gt%3B%3DA1)*%0A%20%20%20%20%20%20%20%20%20%20%20%20%20('Training%20DB'!F2%3AF27%26lt%3B%3DA2)%2C%22%22)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2958310%22%20slang%3D%22en-US%22%3ERe%3A%20Ignore%20duplicates%20with%20multiple%20criteria%20and%20between%202%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2958310%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1216267%22%20target%3D%22_blank%22%3E%40Jules_Rapley%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMPRODUCT((COUNTIF('Training%20DB'!A2%3AA27%2C'Training%20DB'!A2%3AA27)%3D1)*('Training%20DB'!B2%3AB27%3D%22Yes%22)*('Training%20DB'!C2%3AC27%26lt%3B%26gt%3B%22Legacy%22)*('Training%20DB'!G2%3AG27%3D%22Completed%22))%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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 duplicated), Accredited (Yes or No) in Column D, Funding (A.C.T, Legacy, Zero Cost) in Column F, End Date in Column J, and Status (Completed, In Progress, TBC, etc.) in Column L - all on sheet 1 shown below.

 

The stats are on sheet 2 including the between dates which change according to the stats required.  Date 1 in cell A1, date 2 in cell A2.

 

I need to state how many students completed accredited courses that weren't funded by Legacy, between the two dates so that the figures will update on change of dates.

 

Can you help?  Thank you in advance.

 

Jules

Sheet 1

NameAccredited?FundingEnd DateStatus
Nother, AnthonyYesLegacy26/05/2021Completed
Bloggs, JoeYesLegacy26/05/2021Completed
Person, JamesNoLegacy01/07/2021Completed
Peartree, PattyNoLegacy01/07/2021Completed
Nother, AnthonyNoLegacy01/07/2021Completed
Lollipop, DaisyNoLegacy01/07/2021Completed
Dearheart, ANoLegacy01/07/2021Completed
Vimes, SamNoLegacy01/07/2021Completed
Door, BillNoLegacy01/07/2021Completed
Person, JamesYesLegacy06/07/2021Completed
Lollipop, DaisyYesLegacy06/07/2021Completed
Door, BillYesLegacy06/07/2021Completed
Vimes, SamYesLegacy13/07/2021Completed
Bloggs, JoeYesLegacy13/07/2021Completed
Person, JamesYesA.C.T.21/07/2021Completed
Ogg, NannyYesA.C.T.22/07/2021Completed
Peartree, PattyYesLegacy28/07/2021Completed
Nother, AnthonyYesLegacy28/07/2021Completed
Lollipop, DaisyYesLegacy28/07/2021Failed Course
Dearheart, AYesLegacy28/07/2021Completed
Evans, ChristopherYesZero Cost29/07/2021Waiting to Start
Peartree, PattyNoA.C.T.23/08/2021In Progress
Peartree, PattyYesZero Cost30/09/2021Completed
Baird, LogieYesA.C.T.30/09/2021In Progress
Nother, AnthonyNoA.C.T.05/10/2021Completed
Nother, AnthonyNoA.C.T.11/10/2021Completed

 

9 Replies

@Jules_Rapley 

=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?

If you can post a file it would be great

@Quadruple_Pawn 

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.

best response confirmed by Jules_Rapley (Occasional Contributor)
Solution

@Jules_Rapley 

 

 

 

 

=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),""
                    )
              )
         )

 

 

 

 

@Jules_Rapley 

=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"))

Should I add in ('Training DB'!B2:B27,"Yes") to make it accredited only? If so, where should it go?
I just updated both formula and attached file. Check it out please.
Thank you so much! This is perfect!