Custom ADV Filter with IF Function

[BarryBob]

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.

[TABLE="width: 700"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]g[/TD]
[TD]h[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Group[/TD]
[TD]Item[/TD]
[TD]Unit[/TD]
[TD]Standard kit[/TD]
[TD]Additional request[/TD]
[TD]Starting[/TD]
[TD][/TD]
[TD]Starting[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]HQ[/TD]
[TD]Hole Punch[/TD]
[TD]Pack[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]=IF($F1<($E2+$D2)
,"ok","SHORT")[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]HQ[/TD]
[TD]Folder[/TD]
[TD]each[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]HQ[/TD]
[TD]Lock[/TD]
[TD]each[/TD]
[TD]10[/TD]
[TD]2[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]HQ[/TD]
[TD]Lamp[/TD]
[TD]each[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

Hope that makes sense.

 

[Jerry Sullivan]

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")