UNIQUEIF Excel Formula

I recently had an interesting question in a webinar. Could I create a formula like UNIQUEIF. I like a challenge so here is my answer.

The exact question is below.

Q: Is there an easy way to run the equivalent of what would be a UNIQUEIF function? That is, to return any unique items from a Column, but only where they meet a specified criterion?

Solution

In the image below we have a list of states with duplicates. Some are 2 digits and some are 3. The standard UNIQUE function removes duplicates.

The solution involves using the FILTER function to apply the condition and then the UNIQUE function removes duplicates from the FILTER output.

In this case we only want unique states with 2 characters.

The FILTER function can also handle if the condition is based on another column.

In the example dataset below we want to extract the unique invoice numbers for a particular state.

This is a flexible solution – changing cell S1 will change the state being filtered.

The FILTER function is flexible and can handle more than one condition. In the example below we list the WA invoices with an orange product.

The FILTER function offers many solutions when you need to apply conditions in a dynamic array formula.