UNIQUE FILTER formula with multiple criteria

[freeb1893]

So I already have the following formula:

=UNIQUE(FILTER(AI2:AI1500,(AI2:AI1500<>"")*(ISNUMBER(SEARCH(",",AI2:AI1500)))))

This indexes me a list of unique values from Array AI2:AI1500 where the value is not blank and contains a comma.

I want to further enhance this formula and further limit my resulting list of unique values, by also looking at another adjacent array AM2:AM1500 (which is a list of dates) and only returning a unique value if the value on the same row in Array AM2:AM1500 is within the last 60 days,

Anyone know how to tweak the above formula to include this additonal criteria?

 

[KRice]

Try this:

=UNIQUE(FILTER(AI2:AI1500,(AI2:AI1500<>"")*(ISNUMBER(SEARCH(",",AI2:AI1500)))*(AM2:AM1500>=(TODAY()-60))))

 

[Peter_SSs]

This indexes me a list of unique values from Array AI2:AI1500 where the value is not blank and contains a comma.

The check for non-blanks in column AI is pointless since if the cell has to contain a comma, that already establishes that it will not be blank.

if the value on the same row in Array AM2:AM1500 is within the last 60 days,

It is not clear from that whether column AM might contain future dates or not. So one of these may do what you want.

=UNIQUE(FILTER(AI2:AI1500,ISNUMBER(FIND(",",AI2:AI1500))*(AM2:AM1500>=(TODAY()-60)),""))

=UNIQUE(FILTER(AI2:AI1500,ISNUMBER(FIND(",",AI2:AI1500))*(AM2:AM1500>=(TODAY()-60))*(AM2:AM1500<=TODAY()),""))

 

[freeb1893]

Try this:

=UNIQUE(FILTER(AI2:AI1500,(AI2:AI1500<>"")*(ISNUMBER(SEARCH(",",AI2:AI1500)))*(AM2:AM1500>=(TODAY()-60))))

That works! Thank you so much! you're the best!

 

[KRice]

You're welcome...we're happy to help. Peter's point is not lost on me, and you may want to look at his suggestions. The first of those is similar to mine except it omits the requirement for the cells to be "not blank". That's okay because your original formula required cells to contain a comma--which means those cells are not blank, therefore the first filtering criterion (<>"") is not needed.

 

[freeb1893]

Makes sense! I think the blank search was my original logic, then I realized I needed the comma search logic, but didn't remove the blank search after adding the comma search logic, since it didn't break anything, and didn't factor in that the comma logic covers both