Need help counting unique values with multiple criteria without latest version of excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1902545%22%20slang%3D%22en-US%22%3ENeed%20help%20counting%20unique%20values%20with%20multiple%20criteria%20without%20latest%20version%20of%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1902545%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20like%20to%20count%20unique%20values%20that%20meet%20multiple%20criteria.%20I%20assume%20I%20can%20use%20the%20%3Dunique()%20function%3B%20however%20I%20only%20have%20access%20to%20Excel%202016.%20What%20is%20the%20best%20approach%20to%20create%20a%20list%20of%20the%20unique%20values%20in%20column%20A%20that%20match%20all%205%20of%20the%20following%20criteria%20(APPLES%2C%20POTATOES%2C%20STRAWBERRIES%2C%20CUCUMBERS%2C%20MANGOES)%20in%20column%20B.%20Any%20of%20the%20unique%20values%20that%20match%20less%20than%205%20of%20the%20criteria%20should%20not%20be%20listed.%20Also%2C%20the%20best%20approach%20to%20count%20unique%20values%20that%20match%20all%205%20criteria%20them.%20Please%20see%20example.%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22example.JPG%22%20style%3D%22width%3A%20267px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F234471iA2AE81ACB57D258A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22example.JPG%22%20alt%3D%22example.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1902545%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-1902651%22%20slang%3D%22de-DE%22%3ESubject%3A%20Need%20help%20counting%20unique%20values%20with%20multiple%20criteria%20without%20latest%20version%20of%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1902651%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F873798%22%20target%3D%22_blank%22%3E%40cram88%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3CP%3EHow%20many%20unique%20distinct%20products%20did%20Jennifer%20sell%20in%20January%3F%3C%2FP%3E%3CP%3E%3CSTRONG%3EArray%20formula%20in%20C3%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CDIV%3E%3DSUM(IF(%22Jennifer%22%3D%24D-6%3A%24D-27)*(%24B-6%3A%24B-27%26lt%3B%3DDATE(2011%2C%201%2C%2031))%2C%201%2FCOUNTIFS(%24D%246%3A%24D%2427%2C%20%22Jennifer%22%2C%20%24E%246%3A%24E%2427%2C%20%24E%246%3A%24E%2427%2C%20%24B%246%3A%24B%2427%2C%20%22%26lt%3B%3D%22%26amp%3BDATE(2011%2C%201%2C%2031)))%2C%200))%3C%2FDIV%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.get-digital-help.com%2Fcount-unique-distinct-values-that-meet-multiple-criteria-in-excel%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3ECount%20unique%20distinct%20values%20that%20meet%20multiple%20criteria%3C%2FA%3E%3C%2FP%3E%3CP%3E*%20%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3E%20Any%20articles%2C%20templates%2C%20third-party%20products%20or%20information%20I%20have%20provided%20for%20reference%20only.%20While%20I%20endeavor%20to%20keep%20the%20information%20up%20to%20date%20and%20correct%20as%20far%20as%20I%20can.%20I%20make%20no%20representations%20or%20warranties%2C%20express%20or%20implied%2C%20as%20to%20the%20completeness%2C%20correctness%2C%20reliability%2C%20suitability%2C%20or%20availability%20of%20any%20product%20or%20information%2C%20article%2C%20file%2C%20template%2C%20or%20related%20graphic%20on%20my%20posts%20in%20this%20forum.%20The%20trust%20you%20place%20in%20such%20information%20is%20therefore%20entirely%20at%20your%20own%20risk.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1903381%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20counting%20unique%20values%20with%20multiple%20criteria%20without%20latest%20version%20of%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1903381%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F873798%22%20target%3D%22_blank%22%3E%40cram88%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20there%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20follow%20the%20below%20written%20link%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.get-digital-help.com%2Fcount-unique-distinct-values-that-meet-multiple-criteria-in-excel%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.get-digital-help.com%2Fcount-unique-distinct-values-that-meet-multiple-criteria-in-excel%2F%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20should%20help.%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-1904175%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20counting%20unique%20values%20with%20multiple%20criteria%20without%20latest%20version%20of%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1904175%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F873798%22%20target%3D%22_blank%22%3E%40cram88%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20such%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20354px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F234589iB6F72B436C262213%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eto%20create%20list%20of%20unique%20ID%3As%20add%20to%20G3%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR(%0A%20%20%20INDEX(%24B%243%3A%24B%2426%2C%0A%20%20%20%20%20%20AGGREGATE(15%2C6%2C1%2F%0A%20%20%20%20%20%20%20%20%20%20(COUNTIF(%24G%242%3A%24G2%2C%24B%243%3A%24B%2426)%3D0)%2F%0A%20%20%20%20%20%20%20%20%20%20COUNTIF(%24E%243%3A%24E%247%2C%24C%243%3A%24C%2426)%2F%0A%20%20%20%20%20%20%20%20%20%20(COUNTIF(%24B%243%3A%24B%2426%2C%24B%243%3A%24B%2426)%26gt%3B%3D5)*%0A%20%20%20%20%20%20%20%20%20%20(ROW(%24B%243%3A%24B%2426)-ROW(%24B%242))%2C%0A%20%20%20%20%20%201)%0A%20%20%20)%2C%0A%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20it%20down%20till%20empty%20cells%20appear.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I would like to count unique values that meet multiple criteria. I assume I can use the =unique() function; however I only have access to Excel 2016. What is the best approach to create a list of the unique values in column A that match all 5 of the following criteria (APPLES, POTATOES, STRAWBERRIES, CUCUMBERS, MANGOES) in column B. Any of the unique values that match less than 5 of the criteria should not be listed. Also, the best approach to count unique values that match all 5 criteria them. Please see example. example.JPG

 

Thank you.

 

3 Replies

@cram88 

 

Example:

How many unique distinct products did Jennifer sell in January?

Array formula in C3:

=SUM(IF(("Jennifer"=$D$6:$D$27)*($B$6:$B$27<=DATE(2011, 1, 31)), 1/COUNTIFS($D$6:$D$27, "Jennifer", $E$6:$E$27, $E$6:$E$27, $B$6:$B$27, "<="&DATE(2011, 1, 31))), 0))

Count unique distinct values that meet multiple criteria

*Any articles, templates, third-party products or information I have provided are for reference only. While I endeavor to keep the information up to date and correct as far as I can. I make no representations or warranties, express or implied, as to the completeness, correctness, reliability, suitability, or availability of any product or information, article, file, template, or related graphic on my posts in this forum. The trust you place in such information is therefore entirely at your own risk.

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

@cram88 

For such sample

image.png

to create list of unique ID:s add to G3 formula

=IFERROR(
   INDEX($B$3:$B$26,
      AGGREGATE(15,6,1/
          (COUNTIF($G$2:$G2,$B$3:$B$26)=0)/
          COUNTIF($E$3:$E$7,$C$3:$C$26)/
          (COUNTIF($B$3:$B$26,$B$3:$B$26)>=5)*
          (ROW($B$3:$B$26)-ROW($B$2)),
      1)
   ),
"")

and drag it down till empty cells appear.