unable to set the formula array property of the range class

[JLP]

I get the error message 'unable to set the formula array property of the
range class' when I am using a conditional sum function. The data - the
range - is on one worksheet and the result of the calculation is on another
worksheet. If both the data and the result are on the same worksheet, no
error message. Anyone have any ideas?

 

[T. Valko]

Post the formula

 

[JLP]

=SUM(IF(Detail!$J$15:$J$495="DST",IF(Detail!$C$15:$C$495="SAC",IF(Detail!$A$15:$A$495=2008,Detail!$B$15:$B$495,0),0),0))

 

[T. Valko]

Try this formula which is normally entered (not array entered):

=SUMPRODUCT(--(Detail!$A$15:$A$495=2008),--(Detail!$C$15:$C$495="SAC"),--(Detail!$J$15:$J$495="DST"),Detail!$B$15:$B$495)

Better to use cells to hold the criteria:

A1 = 2008
B1 = SAC
C1 = DST

=SUMPRODUCT(--(Detail!$A$15:$A$495=A1),--(Detail!$C$15:$C$495=B1),--(Detail!$J$15:$J$495=C1),Detail!$B$15:$B$495)