Help with slow worksheet (conditional formatting & sumif formulas)

Hi,

I have this worksheet with 6000 rows and 650 columns. There are 10 columns which contain sumif (not sumifs) formulas with a single criteria for all rows from top to bottom. They pull data from a few columns from a second worksheet, which only has about 450 rows.

On the first worksheet I am applying fifteen simple conditional formatting rules. Except for two of the rules all rules only apply to a single column (highlighting certain cells), and the other two rules apply to all columns (highlighting certain rows in all columns). Most rules are very simple and have only one to two formula criteria, some have three to four criteria.

When, on the second worksheet, from which the sumif formulas on the first worksheet pull data, I insert or delete a row, it takes 3 - 6 seconds to do that. This is right after I open the workbook. If I work with the workbook for some time and then insert or delete a row on the second worksheet, it can take 10 - 20 seconds!

Now if I clear all the conditional formatting on the first worksheet I get quite a speed improvement, reducing the above durations from 100% to about 70%. The biggest impact has if I delete the sumif formulas on the worksheet, reducing the above durations further to probably 10-20%.

I tried to insert/delete rows via VBA, turning off calculations/screen updating/event before inserting/deleting and on again after, and setting a cell as an on/off switch for conditional formatting (adding a rule to stop all other rules if the cell says "off") and turning it off via vba before inserting, but it has no effect. My questions are:

1. Why does conditional formatting on the first sheet affect how quickly inserting/deleting happens on the second sheet, when the first sheet isn't even visible while inserting?
2. How can I remove conditional formatting completely via vba and restore it again after inserting/deleting rows (the rule trick with turning off/on again above apparently doesn't have an effect)?
3. Most importantly is there a faster alternative to sumif, preferably via formulas, or otherwise vba? If vba is the only way, what is the general speed gain?

Thanks!

Without seeing a working spreadsheet, I might ask if your conditional formatting and sumif formulas are active in just the ranges that contain data, or include the entire column which has empty cells? If the latter, there's lots of cells being analyzed that don't need to be. Just a thought.
Pete

On the conditional formatting:
- the 13 rules that apply to single column range only, naturally they are active across the 6000 rows of each column range, as they need to monitor the values in those rows
- the 2 rules that apply to the whole worksheet check one or two column ranges (6000 rows each) for the values and format the entire row for each row where the values in those one or two column ranges meet the formatting criteria
- the 6000 rows do have a lot of empty (or 0 value) cells, but all of them need to be checked to see if they need to be highlit as data is added/changed

On the sumif formulas:
- there are 10 columns (6000 rows), each cell in these columns has a sumif function checking one column range on the second worksheet (450 rows) and adding values from a different column range on the second worksheet (450 rows) if the criterium is met.
- The 450 rows on the second worksheet do contain empty cells, but all of them need to be included in the sumif formulas on the first worksheet to be added up once data is entered

Due to complexity and sensitivity I unfortunately can't post the workbook itself, so have to try to describe it.

I was able to speed it up a bit by preventing the sumif formulas from calculating on rows where it does not have to calculate because of missing data (I simply added an IF... statement before the sumif formula to calculate only when a specific criterium is met). It now takes around 3 seconds to insert a row on the second worksheet. Still not great, but faster than before.

I would really like to figure out a VBA solution to populate the columns on the first worksheet. I tried to google for a faster VBA alternative to sumif, and found a link or two which seem to suggest solutions, but am not quite clear how to apply them. Also, they seem to involve sorting the worksheets, which seems a bit awkward. If anyone has any thoughts, please let me know.

Thinking about this just now while typing the above, I think the solution might be to add up all the appropriate values on worksheet two and then find the equivalent on the first worksheet and write the sum to the correct cell, instead of starting with worksheet one and looking for the correct value on worksheet two. That way, I only have to loop through 450 rows instead of 6000. I could do this only when the first worksheet is activated, to take into account any changes made on the second worksheet. Will try that...