I am trying to do a mutiple critera search. If I were using a function it
would either look like this:

=SUM(IF((Markups!A1:A169=C75)*(Markups!B1:B169=X75 )*(Markups!D1:D169=AR75),Markups!E1:E169,0))

or

=INDEX(Markups!$E$2:$E$320,MATCH(AR75,IF(Markups!$ A$2:$A$320=Actuals!C75,Markups!$D$2:$D$320),0))

I've tried to create a custom function to put the formula in the active cell
with no luck.

ActiveCell.FormulaArray =
"=SUM(IF((Markups!A1:A169=C74)*(Markups!B1:B169=X7 4)*(Markups!D1:D169=AR74),Markups!E1:E169,0))"

(In case you are asking why I am using a custom function for a perfectly
good existing function, it's because the custom function evaulates variables
passed and performs a different multiple criteria search based on that).

Any help would be appreciated!

Do you mean you want a UDF that is located in a cell on a worksheet that plops
that formula in the cell?

If yes, then that won't happen. Functions can return values to cells--they
can't change the formula or anything else (almost).

But you could have a sub that populates a bunch of cells with that array
formula.

Elle wrote:

I am trying to do a mutiple critera search. If I were using a function it
would either look like this:

=SUM(IF((Markups!A1:A169=C75)*(Markups!B1:B169=X75 )*(Markups!D1:D169=AR75),Markups!E1:E169,0))

or

=INDEX(Markups!$E$2:$E$320,MATCH(AR75,IF(Markups!$ A$2:$A$320=Actuals!C75,Markups!$D$2:$D$320),0))

I've tried to create a custom function to put the formula in the active cell
with no luck.

ActiveCell.FormulaArray =
"=SUM(IF((Markups!A1:A169=C74)*(Markups!B1:B169=X7 4)*(Markups!D1:D169=AR74),Markups!E1:E169,0))"

(In case you are asking why I am using a custom function for a perfectly
good existing function, it's because the custom function evaulates variables
passed and performs a different multiple criteria search based on that).

Any help would be appreciated!

--

Dave Peterson