Give this person a donut! Thanks it got me where i needed to be. My final
line was:
Range("B6").FormulaArray =
"=SUM(('[file2.xls]Sheet3'!$H$2:$H$1800=1)*('[file2.xls]sheet3'!$K$2:$K$180025))"
I wasn't far off in figuring it out in my first post, but I was missing a
single quote and threw everything off. Thanks.

"kounoike" wrote:

Try this one. Assuming file1.xls and file2.xls are in the same folder.

Range("B6").FormulaArray =
"=SUM(([file2.xls]Sheet3!$H$2:$H$1800=1)*([file2.xls]Sheet3!$N$2:$N$180050))"

keiji

"Bigfoot17" wrote in message
...
I am afraid I was not clear so I will take another stab at it.

I currently have a array formula in file1.xls on sheet1 in cell B6:
={=SUM(('[file2.xls]Sheet3'!$H$2:$H$1800=1)*('[file2.xls]Sheet3'!$N$2:$N$180050))}

This works fine, but now I need to get the same data into the same cell
(from file2) with VBA and cannot get the proper syntax.