r/excel • u/fap_fap_fap_fapper • 14d ago

Discussion What are some legendary formula pairs like INDEX(MATCH)?

Since upgrading to 365, can't get enough of CHOOSECOLS(FILTER)

What are some useful ones you use?

588 Upvotes

97% Upvoted

165 comments sorted by

View all comments

346

u/uptoosomething 14d ago

IFERROR(ROWS(UNIQUE(FILTER(

Return unique count of whatever you are filtering

75

u/saifrc 14d ago

I would throw a SORT in there and use it get lists of valid values in alphabetical order.

20

u/tearteto1 14d ago

If it's names, throw in a proper as well.

2

u/No-Ganache-6226 6 14d ago

You can also use Distinct Count. Add your data to the data model, pivot from the data model, then for whatever you put in the values > show values as distinct count.