Forum Discussion

cram88's avatar
cram88
Copper Contributor
Nov 18, 2020

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

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. 

 

Thank you.

 

excel
Formulas and Functions

3 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Nov 18, 2020

    cram88 

    For such sample

    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.

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Nov 18, 2020

    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.

Resources