# Conditional filter



## bcselect (Dec 15, 2022)

=FILTER('Sheet1'!B13:C212,'Sheet1'!F13:F212=0)

This is a filter I have in a workbook with multiple sheets.  It works fine, but I need it to only function if the corresponding cell value in column D is 1.
Otherwise I need it to do nothing.  Any ideas?


----------



## bcselect (Dec 15, 2022)

bcselect said:


> =FILTER('Sheet1'!B13:C212,'Sheet1'!F13:F212=0)
> 
> This is a filter I have in a workbook with multiple sheets.  It works fine, but I need it to only function if the corresponding cell value in column D is 1.
> Otherwise I need it to do nothing.  Any ideas?


I tried this but get a #Calc error
=FILTER('Sheet1'!B13:C212,('Sheet1'!F13:F212=0)*('Sheet1'!D13:D212=1))


----------



## Fluff (Dec 15, 2022)

What should happen if D1 does not equal 1?


----------



## bcselect (Dec 15, 2022)

Fluff said:


> What should happen if D1 does not equal 1?


I need it to look thru rows 13 to 212 and display filtered items if criteria is met


----------



## bcselect (Dec 15, 2022)

Fluff said:


> What should happen if D1 does not equal 1?


in other words, if the entry in col D isn't 1, nothing should happen.


----------



## Fluff (Dec 15, 2022)

Ok, how about
	
	
	
	
	
	



```
=IF(D1=1,FILTER('Sheet1'!B13:C212,'Sheet1'!F13:F212=0),"")
```


----------



## bcselect (Dec 15, 2022)

Fluff said:


> Ok, how about
> 
> 
> 
> ...


----------



## bcselect (Dec 15, 2022)

this doesn't work at all.  I did insert Sheet1 before the D1 (which should be D13) but still nothing.


----------



## Fluff (Dec 15, 2022)

In what way doesn't it work?


----------



## bcselect (Dec 15, 2022)

bcselect said:


> this doesn't work at all.  I did insert Sheet1 before the D1 (which should be D13) but still nothing.


rather D13:D212


----------



## bcselect (Dec 15, 2022)

=FILTER('Sheet1'!B13:C212,'Sheet1'!F13:F212=0)

This is a filter I have in a workbook with multiple sheets.  It works fine, but I need it to only function if the corresponding cell value in column D is 1.
Otherwise I need it to do nothing.  Any ideas?


----------



## bcselect (Dec 15, 2022)

Fluff said:


> In what way doesn't it work?




```
=IF(‘Sheet1’!D13:D212=1,FILTER('Sheet1'!B13:C212,'Sheet1'!F13:F212=0),"")
```

This is what I tried but excel doesn't see it as a formula


----------



## Fluff (Dec 15, 2022)

Apologies, I misunderstood what you were trying to do.
The formula you posted in post#2 should work, check that you have rows where both D is 1 & F is 0


----------



## bcselect (Dec 15, 2022)

bcselect said:


> ```
> =IF(‘Sheet1’!D13:D212=1,FILTER('Sheet1'!B13:C212,'Sheet1'!F13:F212=0),"")
> ```
> 
> This is what I tried but excel doesn't see it as a formula


----------



## Fluff (Dec 15, 2022)

That is not the correct formula, you need to use the one you posted in post#2


----------



## bcselect (Dec 15, 2022)

this may be the problem.  looks like the formula doesn't like zeros.  when I changed all zeros to twos (formula and actual entries) it works.


----------



## bcselect (Dec 15, 2022)

bcselect said:


> ```
> =IF(‘Sheet1’!D13:D212=1,FILTER('Sheet1'!B13:C212,'Sheet1'!F13:F212=0),"")
> ```
> 
> This is what I tried but excel doesn't see it as a formula


----------



## bcselect (Dec 15, 2022)

bcselect said:


> this may be the problem.  looks like the formula doesn't like zeros.  when I changed all zeros to twos (formula and actual entries) it works.


but the entire workbook is based on certain entries being zeros so I can't easily change it.


----------



## Fluff (Dec 15, 2022)

Which formula are you talking about?


----------



## bcselect (Dec 15, 2022)

=FILTER('Sheet1'!B13:C212,'Sheet1'!F13:F212=0)


----------



## Fluff (Dec 15, 2022)

If you want to only pull rows where D is 1 as well then you should be using this formula you posted
	
	
	
	
	
	



```
=FILTER('Sheet1'!B13:C212,('Sheet1'!F13:F212=0)*('Sheet1'!D13:D212=1))
```


----------



## bcselect (Dec 15, 2022)

=FILTER('Sheet1'!B13:C212,'Sheet1'!F13:F212=0)

This is a filter I have in a workbook with multiple sheets.  It works fine, but I need it to only function if the corresponding cell value in column D is 1.
Otherwise I need it to do nothing.  Any ideas?


----------



## bcselect (Dec 15, 2022)

Fluff said:


> If you want to only pull rows where D is 1 as well then you should be using this formula you posted
> 
> 
> 
> ...





Fluff said:


> If you want to only pull rows where D is 1 as well then you should be using this formula you posted
> 
> 
> 
> ...





Fluff said:


> If you want to only pull rows where D is 1 as well then you should be using this formula you posted
> 
> 
> 
> ...


=FILTER('Sheet1'!B13:C212,('Sheet1'!F13:F212=0)*('Sheet1'!D13:D212=1))  This is the one I'm using but it returns a value error, I think because of the zeros.


----------



## bcselect (Dec 15, 2022)

I really want to thank you for hanging in there with me.  I know it can get frustrating.


----------



## Fluff (Dec 15, 2022)

I fyou are getting a #value error, that suggests that you have errors like that in cols D and or F


----------

