Thanks:  0
Likes:  0

Thread: CSE(array copying problem)

1. 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!!

2. 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

3. 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!!

4. 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

5. 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 ]

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•