Formula for counting duplicate rows only once combined with multiple criteria.

%3CLINGO-SUB%20id%3D%22lingo-sub-394241%22%20slang%3D%22en-US%22%3EFormula%20for%20counting%20duplicate%20rows%20only%20once%20combined%20with%20multiple%20criteria.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394241%22%20slang%3D%22en-US%22%3E%3CP%3EHere's%20what%20I'm%20trying%20to%20do.%20I%20have%20a%20worksheet%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CSTRONG%3EID%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EDate%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3ETransfer%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EOther%3C%2FSTRONG%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12345%3C%2FTD%3E%3CTD%3E5%2F4%2F2019%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12345%3C%2FTD%3E%3CTD%3E5%2F4%2F2019%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12345%3C%2FTD%3E%3CTD%3E5%2F4%2F2019%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E98789%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E98789%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E65454%3C%2FTD%3E%3CTD%3E5%2F4%2F2019%3C%2FTD%3E%3CTD%3EY%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E91595%3C%2FTD%3E%3CTD%3E5%2F4%2F2019%3C%2FTD%3E%3CTD%3EY%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E75357%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EY%3C%2FTD%3E%3CTD%3E9%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E75357%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EY%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20would%20like%20is%20a%20%3CSTRONG%3Eformula%26nbsp%3B%3C%2FSTRONG%3Eto%20count%20every%20ID%20one%20time%20that%20has%20a%20value%20in%20the%20Date%20column%20and%20no%20value%20in%20the%20Transfer%20column.%20All%20rows%20will%20always%20have%20an%20ID%20and%20at%20least%20some%20other%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20problem%20is%20that%20I%20can%20get%20a%20count%20of%20all%20rows%20what%20have%20a%20value%20in%20Date%20and%20no%20value%20in%20Transfer%2C%20but%20it%20includes%20duplicate%20ID%20entries%2C%20which%20I%20don't%20want.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20the%20table%20above%2C%20I%20would%20like%20to%20return%20the%20value%20%221%22%20because%20ID%2012345%20represents%201%20person%2C%20but%20I%20can%20only%20manage%20to%20get%20the%20value%20%223%22%20because%20ID%2012345%20is%20repeated%203%20times.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20that%20makes%20sense.%20I%20know%20I%20could%20get%20the%20answer%20I'm%20looking%20for%20by%20removing%20duplicates%2C%20but%20I'd%20like%20to%20avoid%20that%20if%20possible.%20Thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-394241%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-394373%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20counting%20duplicate%20rows%20only%20once%20combined%20with%20multiple%20criteria.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394373%22%20slang%3D%22en-US%22%3EYou%20can%20use%20this%20formula%3A%3CBR%20%2F%3E%3DSUMPRODUCT(1%2FCOUNTIF(A2%3AA10%2CA2%3AA10)*%3CBR%20%2F%3E(B2%3AB10%26lt%3B%26gt%3B%22%22)*%3CBR%20%2F%3E(C2%3AC10%3D%22%22))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1507296%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20counting%20duplicate%20rows%20only%20once%20combined%20with%20multiple%20criteria.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1507296%22%20slang%3D%22en-US%22%3E%3CP%3E-ERR%3AREF-NOT-FOUND-%40Twifoo%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20similar%20to%20something%20I'm%20trying%20to%20figure%20out%2C%20but%20the%20formula%20you%20describe%20doesn't%20work%20for%20me.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20working%20with%20content%20in%20which%20I'm%20trying%20to%20get%20a%20count%20of%20specific%20types%20of%20training%20but%20only%20count%20the%20unique%20sessions.%26nbsp%3B%20This%20is%20a%20subset%20of%20the%20data%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22456%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22152.667px%22%3E%3CSTRONG%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EType%20of%20T%2FTASession%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22302.667px%22%3E%3CSTRONG%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3ETitle%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22152.667px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EIn%20Person%20Presentation%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22302.667px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EKey%20Financial%20Management%20Concepts%20and%20Processes%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22152.667px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EIn%20Person%20Presentation%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22302.667px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EBudget%20Review%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22152.667px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EOffice%20Hours%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22302.667px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EBudget%20Review%2C%20TA%20call%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22152.667px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3ELive%20Webinar%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22302.667px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EBest%20Practices%20in%20Budget%20Development%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22152.667px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EOffice%20Hours%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22302.667px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EBudget%20Review%2C%20TA%20call%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22152.667px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3ELive%20Webinar%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22302.667px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EGO%20Certification%20Steps%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22152.667px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3ELive%20Webinar%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22302.667px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EGO%20Certification%20Steps%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22152.667px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3ELive%20Webinar%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22302.667px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EJanuary%202020%20Third%20Thursdays%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22152.667px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3ERecorded%20Instruction%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22302.667px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EJanuary%202020%20Third%20Thursdays%26nbsp%3B%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22152.667px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3ERecorded%20Instruction%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22302.667px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EJanuary%202020%20Third%20Thursdays%26nbsp%3B%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22152.667px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EInstruction%20only%2C%20no%20demo%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22302.667px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EManual%20Holds%20and%20Manual%20Reviews%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22152.667px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3ELive%20Webinar%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22302.667px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EJune%202020%20Third%20Thursdays%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22152.667px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EInstruction%20only%2C%20no%20demo%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22302.667px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EPre-Award%20Spending%20Approval%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22152.667px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EInstruction%20only%2C%20no%20demo%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22302.667px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EManual%20Holds%20and%20Manual%20Reviews%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20where%20the%20%22Office%20Hours%22%20includes%20two%20entries%20for%20%22Budget%20Review%2C%20TA%20call%22%2C%20I%20only%20want%20to%20count%20the%20value%20of%20one%2C%20since%20the%20second%20is%20a%20repeated%20session.%26nbsp%3B%20I%20tried%20using%20COUNTIFS%20but%20need%20the%20ability%20to%20automatically%20filter%20the%20duplicates.%26nbsp%3B%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EAny%20ideas%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Here's what I'm trying to do. I have a worksheet like this:

 

IDDateTransferOther 
123455/4/2019 5
123455/4/2019 4
123455/4/2019 6
98789  6
98789  4
654545/4/2019Y2
915955/4/2019Y1
75357 Y9
75357 Y1

 

What I would like is a formula to count every ID one time that has a value in the Date column and no value in the Transfer column. All rows will always have an ID and at least some other data.

 

My problem is that I can get a count of all rows what have a value in Date and no value in Transfer, but it includes duplicate ID entries, which I don't want.

 

With the table above, I would like to return the value "1" because ID 12345 represents 1 person, but I can only manage to get the value "3" because ID 12345 is repeated 3 times.

 

I hope that makes sense. I know I could get the answer I'm looking for by removing duplicates, but I'd like to avoid that if possible. Thanks

2 Replies
You can use this formula:
=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10)*
(B2:B10<>"")*
(C2:C10=""))
Highlighted

@Twifoo 

 

This is similar to something I'm trying to figure out, but the formula you describe doesn't work for me.  

 

I'm working with content in which I'm trying to get a count of specific types of training but only count the unique sessions.  This is a subset of the data:

 

Type of T/TASessionTitle
In Person PresentationKey Financial Management Concepts and Processes
In Person PresentationBudget Review
Office HoursBudget Review, TA call
Live WebinarBest Practices in Budget Development
Office HoursBudget Review, TA call
Live WebinarGO Certification Steps
Live WebinarGO Certification Steps 
Live WebinarJanuary 2020 Third Thursdays 
Recorded InstructionJanuary 2020 Third Thursdays  
Recorded InstructionJanuary 2020 Third Thursdays  
Instruction only, no demoManual Holds and Manual Reviews
Live WebinarJune 2020 Third Thursdays 
Instruction only, no demoPre-Award Spending Approval 
Instruction only, no demoManual Holds and Manual Reviews

 

So where the "Office Hours" includes two entries for "Budget Review, TA call", I only want to count the value of one, since the second is a repeated session.  I tried using COUNTIFS but need the ability to automatically filter the duplicates.  

Any ideas?