complex excel formula Array how do I convert it to a vba Function

[Guest]

I have an Array Formula:{=SUM(IF((B2:B7="New") * (D27="Test1")*(A2:A7="Band
A"),C2:C7*E2:E7)) + SUM(IF((B2:B7="New") * (F2:F7="Test1")*(A2:A7="Band
A"),C2:C7*G2:G7))} in a cell, I have managed to alter this for use in VBA to
change variables eg """ & Var & """ but want to do it as a WorksheetFunction
so I don't have to add the formula to cells to get the answer.

thank you for any help you can give.

 

[Harlan Grove]

Rob wrote...

I have an Array Formula:{=SUM(IF((B2:B7="New") * (D27="Test1")
*(A2:A7="Band A"),C2:C7*E2:E7)) + SUM(IF((B2:B7="New") *
(F2:F7="Test1")*(A2:A7="Band A"),C2:C7*G2:G7))} in a cell, . . .

You could simplify this to the nonarray formula

=SUMPRODUCT((A2:A7="Band A")*(B2:B7="New")*C2:C7*{1,0,1},
--(D2:F7="Test1"),E2:G7)

. . . I have managed to alter this for use in VBA to
change variables eg """ & Var & """ but want to do it as a WorksheetFunction
so I don't have to add the formula to cells to get the answer.

So you want to make this a user-defined function you could call from
cell formulas? You'd be better off using your original formula or the
alternative I gave. Udfs are slow. Also, unless you add a fair amount
of error checking to udfs, they only return #VALUE! errors when
anything goes wrong. Your formula above would return a somewhat more
useful #N/A if one of the ranges spanned a different number of rows
than the other.