Author: Oscar Cronquist Article last updated on August 15, 2017

Sara asks:

How can you use large with multiple criteria?? Example looking for top 5 of a list based on name, state and city..


Array formula in cell E3:

=INDEX($A$2:$C$29, MATCH(LARGE(IF(COUNTIFS($E$2:E2, $A$2:$A$29, $F$2:F2, $B$2:$B$29, $G$2:G2, $C$2:$C$29)=0, COUNTIFS($A$2:$A$29, $A$2:$A$29, $B$2:$B$29, $B$2:$B$29, $C$2:$C$29, $C$2:$C$29), ""), 1), COUNTIFS($A$2:$A$29, $A$2:$A$29, $B$2:$B$29, $B$2:$B$29, $C$2:$C$29, $C$2:$C$29)*(COUNTIFS($E$2:E2, $A$2:$A$29, $F$2:F2, $B$2:$B$29, $G$2:G2, $C$2:$C$29)=0), 0), COLUMN(A1))

Formula in cell H3:

=COUNTIFS($A$2:$A$29, E3, $B$2:$B$29, F3, $C$2:$C$29, G3)

How to create an array formula

  1. Select cell E3
  2. Paste array formula
  3. Press and hold Ctrl + Shift
  4. Press Enter

How to copy array formula

  1.  Select cell E3
  2. Copy cell (not formula)
  3. Select cell range F3:G3
  4. Paste
  5. Select cell range E3:G3
  6. Copy cells (not formulas)
  7. Select cell range E4:G9
  8. Paste

Explaining array formula in cell E3

See post: Create a unique distinct list and sort by occurrances from large to small

Download example *.xlsx file

Unique distinct records sorted by frequency.xlsx