# Custom ADV Filter with IF Function

#### BarryBob

##### New Member
I'm trying to use a adv filter to pull out, in a separate table, items from a table which meet a criteria based on a formula

ie. the criteria should look at 'starting column' F, and is only if that number is smaller than the sum of columns D & E.
If tried a IF Formula but cant seem to get it to work.

=IF(\$F1<(\$E2+\$D2),"ok","SHORT")

The IF formula seems to work by its self, but i cannot seem to get it to work with the adv filter.

 A B C D E F g h 1 Group Item Unit Standard kit Additional request Starting Starting 2 HQ Hole Punch Pack 2 4 2 =IF(\$F1<(\$E2+\$D2) ,"ok","SHORT") 3 HQ Folder each 10 0 10 4 HQ Lock each 10 2 10 5 HQ Lamp each 2 1 3

<tbody>
</tbody>

The adv filter is applied to the full table A1:F5 with the criteria in H2

Hope that makes sense.

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### Jerry Sullivan

##### MrExcel MVP
Hi BarryBob and Welcome to the Board,

When using advanced filter with a formula-based criteria, the rows in the List Range that will be copied are those for which the formula evaluates to TRUE.

So one reason that you're not getting the desired results is that your formula will only evaluate to "ok" or "Short", so no TRUE rows will be copied.
Try replacing the formula in H2 with this...
=\$F1<(\$E2+\$D2)

You'll also need to have Cell H1 empty, then reference H1:H2 as the Criteria Range to get your desired result.

Just to illustrate the concept, this IF formula would also work; however that's a more complicated way to get the same result.
=IF(\$F1<(\$E2+\$D2),TRUE,"Short")

Replies
3
Views
615
Replies
3
Views
73
Replies
2
Views
93
Replies
6
Views
135
Replies
6
Views
161

1,195,651
Messages
6,010,929
Members
441,573
Latest member
Goronvir

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back