further develop the =UNIQUE formula

Question

in Excel, could we build on the =UNIQUE formula and have =UNIQUEIF and =UNIQUEIFS

with my current problem, I am looking at data spanning several years and I want to create a unique list of people for any given year/month at a time. by having a UNIQUEIF or IFS, i could link the IF part of the function to a cell with the desired year or month allowing my boss to get the answers he wants quickly and without having to ask me to do it for him.

The reason I am using =UNIQUE in the first place is so that I dont have to modify the sheet when new people appear or people disappear from the data being queries

Answer 1

You can filter the original array by whatever criteria you like and pass the filtered array to unique to extract the relevant data.

Answer 2

Hi @Andy M

Could you please give us a simple sample?

To make your formulas easier to manage and more flexible as data grows, I suggest you convert your data ranges to Tables:

Select your data range, press Ctrl + Q > Select "Tables" tab > Table.

This way, any new data added to the table will automatically be included in your formula without you needing to adjust the range each time.

In my sample, I named this table as " Table1" like the following image.

Then as Barry Schwarz suggested, you may combine Unique functions with Filter functions.

If you want to filter by a specific month and year, such as November 2024, you can try the formula: =UNIQUE(FILTER(Table1[Name], (YEAR(Table1[Date]) = 2024) * (MONTH(Table1[Date]) = 11))).

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---