Array Formula in VBA with SUM and IF not working

[jacksongf]

I'm trying to apply an array formula to a range of cells, however it gives me the classic "Unable to set the FormulaArray property of the Range class" error.

Here is the equation:

ActiveSheet.Cells(i, 30).FormulaArray = "=Sum(If('" & Path & "\[" & FileName & "]" & SheetName & "'!" & "B5" & ":" & "B371" & "=" & """Mon""" & "," & "'" & Path & "\[" & FileName & "]" & SheetName & "'!" & "T5" & ":" & "T371" & "," & "0" & "))"

The weird thing is, if I take out the = sign before the Sum, then it will paste this formula as text in the cells, and then if I insert an equal sign manually and press ctrl + alt + enter, it works perfectly.

So why isn't it working in VBA then?

Any help is greatly appreciated!

 

[Marcelo Branco]

To put an array formula in a multi-cell range is a little bit tricky. You should put the formula in the first cell of the range and then copy (or fill down) to the others cells. You also can use a loop.

Take a look at this excellent article by Colin Legg
https://colinlegg.wordpress.com/2012/05/23/working-with-range-formulaarray-in-vba/

M.

 

[jacksongf]

Thanks for the response Marcelo,

The problem is that simply filling down or looping won't work, because the formula relies on what's in the row with it (the variables Path, Filename, Sheetname etc). It need's to lookup to a certain path, file name, and sheet name withing that file. Unless there's a way to make this work in a formula that can be pulled down... I'll play around with it a bit.

I'll also read the article you posted. Thanks!

Jackson

 

[jacksongf]

Couldn't get anything to work. This is a tough one. Any more ideas out there?

 

[Marcelo Branco]

It's hard to help...
Some questions:
Why are you using an array formula like SUM(IF(...? Couldn't you use SUMIFS(...?
Also i don't understand
..& SheetName & "'!" & "B5" & ":" & "B371" & "=" & """Mon""" & ","...
Why not?
..& SheetName & "'!B5:B371=""Mon"",...

M.

 

[Marcelo Branco]

Complementing my previous post
I know SUMIFS doesn't work with closed workbooks, but maybe you can build an equivalent formula with SUMPRODUCT that works perfectly with closed workbooks, avoiding an Ctrl+Shift+Enter formula

Just thoughts...as i said is hard to help..

M.

 

[jacksongf]

I'm using SUM(IF( because of the closed workbook issue, and I believe the formula with B5:B371 as not text would work as well, I just decided to keep everything as text and input it like that, but what you said would work as well I think.

I am not very familiar with the SUMPRODUCT formula, how would I go about using that to achieve my goal and replace the need for an array formula?

No worries, I realize it is hard to help, but I greatly appreciate your responses.

 

[Marcelo Branco]

Example of SUMPRODUCT equivalent to SUMIFS

Suppose you have
=SUMIFS(C2:C10,A2:A10,criteria1,B2:B10,criteria2)

an equivalent formula would be
=SUMPRODUCT(C2:C10,--(A2:A10=criteria1),--(B2:B10=criteria2))

For a very good explanation about SUMPRODUCT with multiple criteria take a look at
http://xldynamic.com/source/xld.SUMPRODUCT.html

M.

 

[jacksongf]

SUMPRODUCT worked like a charm! Thank you! It did exactly what I needed it to do.

Jackson

 

[Marcelo Branco]

SUMPRODUCT worked like a charm! Thank you! It did exactly what I needed it to do.

Jackson

Hi Jackson

Great you found a solution!

You are welcome. Glad to help

M.