CSE(array copying problem)

[EdE]

Hi All --
Goofy question. I have an array formula that when I drag and drop copy to more than one cell, when I save Excel crashes. I can do it one cell at a time. Here are the 3 instances:
=SUM(IF($C$60:$C$80=1,IF($F$60:$F$80<=H$56,$E$60:$E$80,0),0))
=SUM(IF($C$60:$C$80=2,IF($F$60:$F$80<=H$56,$E$60:$E$80,0),0))
=SUM(IF($C$60:$C$80=3,IF($F$60:$F$80<=H$56,$E$60:$E$80,0),0))

where h56 is a date. I want to drag the column to copy but crashes when I save. I am using Excel97. (I know I know, dont have a choice though.)

Thanks!!

[Asala42]

Hiya,

Can you try a similar formula using sumproduct? Something like:

=Sumproduct(($C$60:$C$80=1)+0,($F$60:$F$80<=H$56)+0,$E$60:$E$80)

Worth a shot

You should be able to run array formulas w/o crashing though. Does this only happen in that one file? What happens if you toss some test data into a blank xls file and try the same array formulas? At minimum, I guess you could try deleting *.tmp files, running scandisk, and maybe a defrag when you have time.

Hope that helps somewhat
Adam

[EdE]

Thanks Adam. I have this problem no matter the file. I can copy one Row at a time but not a column. Your formula works great though. Thanks!!

[Chris Davison]

Ed,

also, the only incremental there is the 1,2,3 in your arrayed-IF statement

I have no clue to the internal mechanisms of a computer, but maybe this is what's causing the problem...... there's something adrift in your computer's ability to auto-increment by 1 each time...

try substituting the 1,2,3,4 etc with "row(A1)"

this will ask excel to give you the row number of row1, row2, row3 etc etc ad finitum which may be a different non-crashable function or DLL or something

[Dave Patton]

Should H$56 be $H$56? Do you want all date <=H56?

The following both give the same result; Array enter.

=SUM(IF(($C$60:$C$80=1)*($F$60:$F$80<=$H$56),$E$60:$E$80),0)

=SUM(($C$60:$C$80=1)*($F$60:$F$80<=H56)*($E$60:$E$80))


[ This Message was edited by: Dave Patton on 2002-04-10 16:25 ]