# Using FILTER Function that ingnores blank Criteria cells if no values in it



## albers1291 (Dec 20, 2022)

PART 1) I have a workbook that we keep track of all help hired along with a bunch of other information. I wanted to create a tab that will use the following criteria (State, Date Range, Used Multiple Times, Rehire Status) to retrieve the names of anyone who fit the criteria that is filled in. I have used the Filter Function to retrieve a list of values using the criteria as long as all criteria are filled in. If i leave one of the cells blank, then it wont retrieve any values. What i want to do (whether its using the FILTER function or something else), is retrieve a list of unique values that meet whatever criteria is entered and to ignore the criteria that is blank. The current equation i am using is as follows: =SORT(UNIQUE(FILTER('Help Slip information'!$A$2:$AD$20000,('Help Slip information'!$A$2:$A$20000=$B$1)*('Help Slip information'!$P$2:$P$20000=$B$5)*('Help Slip information'!$E$2:$E$20000>=$B$2)*('Help Slip information'!E2:E20000<=$B$3),"None Meet Search Criteria")))

Part 2) The second part of this that i have no idea how to even start doing is to create a formula that pulls in only names of people that we have used multiple times over a certain time period in a specific state. In an ideal world, I would like it incorporated into the above formula, but I could live with having a separate tab for just this list.


----------



## Fluff (Dec 20, 2022)

Hi & welcome to MrExcel.
How about
	
	
	
	
	
	



```
=SORT(UNIQUE(FILTER('Help Slip information'!$A$2:$AD$20000,(IF(B1="",1,'Help Slip information'!$A$2:$A$20000=$B$1))*(IF(B5="",1,'Help Slip information'!$P$2:$P$20000=$B$5))*(IF(B2="",1,'Help Slip information'!$E$2:$E$20000>=$B$2))*(IF(B3="",1,'Help Slip information'!E2:E20000<=$B$3)),"None Meet Search Criteria")))
```


----------



## albers1291 (Dec 20, 2022)

Fluff said:


> Hi & welcome to MrExcel.
> How about
> 
> 
> ...


This worked perfectly!! Thank you so much!!!


----------



## Fluff (Dec 20, 2022)

You're welcome & thanks for the feedback.


----------

