excel - error set array formula

Hey All,

tell application "Microsoft Excel"
	--Formula 1
	-- Target Formula but receive an error
	--set formula array of cell "A1" to "=IF(SUM((F16:F194<0)*(G16:G194=L3)*(D16:D194<>\"VALUE ONE\")*(D16:D194<>\"VALUE TWO \")*1)=0,\"\",SUM((F16:F194<0)*(G16:G194=L3)*(D16:D194<>\"VALUE ONE\")*(D16:D194<>\"VALUE TWO\")*1))"
	-- Formula 2
	--Works Removing the last part 
	-- SUM((F16:F194<0)*(G16:G194=L3)*(D16:D194<>\"VALUE ONE\")*(D16:D194<>\"VALUE TWO\")*1) 
	--and replacing it by "1" or anything
	--Suspecting something might not be coded right in last part but unsure what mistake I've made
	set formula array of cell "A2" to "=IF(SUM((F16:F194<0)*(G16:G194=L3)*(D16:D194<>\"VALUE ONE\")*(D16:D194<>\"VALUE TWO \")*1)=0,\"\",1)"
end tell


  1. set a formula array to a cell.


  1. Receiving error: "Microsoft Excel got an error: Can’t set formula array of cell “A1” to …
  2. Converting the working excel formula to an applescript formula I receive the error above

What works:

  1. using Excel and typing this formula:
    “=IF(SUM((F16:F194<0)(G16:G194=L3)(D16:D194<>“VALUE ONE”)(D16:D194<>"VALUE TWO ")1)=0,“”,SUM((F16:F194<0)(G16:G194=L3)(D16:D194<>“VALUE ONE”)*(D16:D194<>“VALUE TWO”)*1)”

then pressing command+return makes this an formula array WHICH WORKS!

  1. Formula 2 works but only if you remove the last part of the formula
    SUM((F16:F194<0)(G16:G194=L3)(D16:D194<>"VALUE ONE")*(D16:D194<>"VALUE TWO")*1) and you can replace it with anything and the script works


  1. I’m suspecting there is an issue with a character in there as I learned you have to "" all " to “"” so there might be something I’m not seeing.

Any help like always would be appreciated.

Thanks for the direction Jacques as always greatly appreciated.

Here’s another solution

tell application "Microsoft Excel"
		set formula_1 to "=IF(SUM((F16:F194<0)*(G16:G194=L3)*(D16:D194<>\"VALUE ONE\")*(D16:D194<>\"VALUE TWO\")*1)=0,\"\",SUM((F16:F194<0)*(G16:G194=L3)*(D16:D194<>\"VALUE ONE\")*(D16:D194<>\"VALUE TWO\")*1))"
		set the clipboard to formula_1
		select cell "A1"
		tell application "System Events"
			keystroke "v" using {command down}
			keystroke "u" using control down
			keystroke return using {command down}
		end tell
end tell