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...
excel
Formulas and Functions
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.

Resources