ARRAY FORMULAS IN ADVANCED FILTER ("CRITERIA")

Not being Aladin, I'm tempted not to answer, but what the hell...

...why don't you tell us what you are actually trying to do?

Excuse Me.

What it interests to me is a theoretical answer. SIMPLY: CAN BE USED ARRAY formulas lIKE CRITERIA IN ADVANCED FILTERS?


(The replacement by SUMAPRODUCTO WORKS. THAT ALREADY)

THANKS

GALI

Excuse Me.

What it interests to me is a theoretical answer. SIMPLY: CAN BE USED ARRAY formulas lIKE CRITERIA IN ADVANCED FILTERS?


(The replacement by SUMAPRODUCTO WORKS. THAT ALREADY)

THANKS

GALI

Click to expand...

The answer is no. The proc for Advanced Filter runs the formula specified in the criteria range on every record. If the formula is a formula that requires control+shift+enter, the proc appears not to know what to do with "control+shift+enter." A limited search did not reveal any explicit note on having control+shift+enter'ed formulas in the criteria range. The issue is not that it has a problem with array objects as the case with SumProduct as you noticed proves. The following example corroborates this observation:

The formula in the criteria range, in F3, is:

=EVAL(($B3-AVERAGE(IF($A$3:$A$9=A3,$B$3:$B$9)))>=$E$1)

If you remove EVAL...

=($B3-AVERAGE(IF($A$3:$A$9=A3,$B$3:$B$9)))>=$E$1

and confirm the formula with control+shift+enter, an empty result list is all what we get.

Conclusion: No CSE-formulas in the criteria range.

Note that this also confirms Paddy's experience reported in your repost-thread.

Thank you, Aladin. Your answer has been very clear and very clear the "contra - example".