Array Formula

[sharon t]

I am receiving a Run-time error "1004": unable to set the FormulaArray
property of the Range class.

Stated fix is to install SP2. I have SP3 installed. Does anyone know if SP3
included that fix that was in SP2? If so, why am I still receiving the error
and the formula does not work correctly. Thanks.

 

[Rick Rothstein \(MVP - VB\)]

It might help if you show us your code

Rick

 

[sharon t]

=SUM(IF('Grand Total-All Detail'!$B$3:$B$14="Jane",IF('Grand Total-All
Detail'!$A$3:$A$14="Doe",IF('Grand Total-All Detail'!$C$3:$C$14=2007,'Grand
Total-All Detail'!$L$3:$L$14,0),0),0))

The "Grand Total-All Detail" is one of many spreadsheets in the workbook and
am writing the array formula in another "summary" spreadsheet. It will work
if I enter on same spreadsheet but don't want it there. Have always been able
to write this formula to show summaries on a separate spreadsheet within the
same workbook.

 

[Rick Rothstein \(MVP - VB\)]

Hopefully someone more knowledgeable about this stuff will come along to
help you; however, that formula appears to work correctly on my system. I'm
not sure if this will help you to focus in on the problem or not; but I do
note that the original error message you reported, namely...

"Run-time error "1004": unable to set the
FormulaArray property of the Range class"

looks like a VBA error message (from, say, a macro or UserForm code module)
and not a worksheet error message. Do you have any VBA code running that is
trying to set a FormulaArray property of a range (perhaps one that is
overlapping the cell(s) you are putting your formula into)?

Rick

 

[sharon t]

No VBA code running which is trying to set a FormulaArray property of a
range. The fix is out under Help and support, article ID 885245. States SP2
fixes this error. I have SP3 loaded since updated computer a couple weeks ago
and now receiving this error. I would think SP3 would contain all fixes
included in SP2 but doesn't appear to be the case. Never had the problem
prior to upgrade. I use the conditional sum array formula all the time-never
had a problem prior to this.

 

[Rick Rothstein \(MVP - VB\)]

According to the download page for SP3, it has SP2 fixes included (as well
as SP1 also). I don't know if this has anything to do with your problem or
not, but putting "Features that are unavailable in shared workbooks" in the
Help Search field in the worksheet window and clicking that same article
title says that for shared workbooks, you cannot "change or delete array
formulas" but that "existing array formulas continue to calculate
correctly". I also did a quick Google search and found a reference to that
same error (but from a "hiding" issue) and the solution involved removing a
Comment that was attached to one of the cells. Just figured I would mention
these "in case".

Rick

 

[sharon t]

Thanks for the info. Not a shared workbook and no comments in any of the
cells. It is a "monster" workbook, 24 spreadsheets in it, thousands of links
and full of array formulas. Everything worked fine until I had my computer
upgraded, they downloaded SP3 (the fix was in SP2) and now I'm getting the
error. It will work if I stay of the spreadsheet where all the data is but
when trying to use the conditional sum to enter the info on the summary
sheet, it gives me the error.

 

[T. Valko]

Rick knows more about VBA than I do but the error message you're getting is
not one that you should get by typing/entering a formula in a cell.

when trying to use the conditional sum to enter the info
on the summary sheet, it gives me the error.

Does that mean you're using the conditional sum wizzard?

What happens if you don't use the conditional sum wizzard and just type the
formula in?

Also, this can be done with a non-array formula (normally entered):

=SUMPRODUCT(--('Grand Total-All Detail'!$B$3:$B$14="Jane"),--('Grand
Total-All
Detail'!$A$3:$A$14="Doe"),--('Grand Total-All
Detail'!$C$3:$C$14=2007),'Grand
Total-All Detail'!$L$3:$L$14)

 

[sharon t]

I have tried both with the conditional sum wizzard and entering the actual
formula in. It's looking more and more like the file may be corrupted as I
can enter this into a blank, new workbook and it works fine. This all
happened when SP3 was loaded on my computer so don't know if that is what
caused the problem or not. Workbook worked fine up until that point. Getting
it restored and will re-write the last three spreadsheets within the
workbook. Thanks to all who gave assistance.