Excel 2010 Copy-Paste SUMIF Slow

[mrstaggart]

If I use SUMIF or CountIF formulas then paste to the 20 cells in a filtered list, it takes 5 minutes (I've timed it with a stopwatch) and one of my dual CPUs is pegged the whole time. I have turned off auto-calculation and it makes no difference. As far as I can tell, it seems to be taking this long to modify the range of cells in the formulas (changing A4:A104 to A6:A106). Any advise on settings to change or other things to look for that can speed this up so it doesn't take hours to copy a formula to 400 cells? (which, when a coworker does it in 2007, takes 10 seconds)?

Sample formula to show total revenue only for clients that are "Include" = YES:
=if(and(A2=A3, c2="YES"), sumif(A3:A103, A2, b3:b103), 0)

Filter for Blank in Column B then paste to all three rows - it takes 2 minutes!

A B C
Client ID Revenue Include?
101 <formula> YES
101 3215
101 502
101 199
101 3717
102 <formula>
102 382
102 114
102 375
102 400
102 29
102 675
103 <formula> YES
103 199
103 332
103 520
103 798
103 119

 

[SkipVought]

hi,

wow! Do you have other workbooks open?

Have you rebooted?

Coupla hundred rows is a very small table to calculate.

Why are you pasting into a FILTERED table, that is assuming that the filter is hiding rows? Otherwise why is that a factor?

FYI, I don't think that it a particularly good practice to have TEXT & NUMBERS in the same column, as your formula returns.

Do you have a bunch or hidden formulas?

What's the file size?

You might reconstruct the workbook, if its feasible.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mrstaggart]

Thank you for the quick reply.

This file is a model to take all client revenue order data and then summarize it without using subtotals because the summary or the details are used by others; subtotal functions won't work in the other views/summaries/user processes. So, I filter, paste, then unfilter for use as a master model. I think filtering is an issue because the formula paste is then to non-contiguous rows; I'll try pasting the formula to contiguous rows to see if it makes a difference.

Yes, this is the only workbook open; actually, the only thing open (no e-mail, browsers, word, etc.). The workbook is 560k. Rebooting makes no difference. It is not calculation either - I turn it to manual and it makes no difference. I unplugged from the network, rebooted, then tried again without network in case the work network has stuff running/indexing/scanning and still no difference.

I wonder if Excel 2010 has a new setting around row numbering, iterative calculations, or an error checking rule that may be contributing to this? I can't find any. Next option is that it is on my pc or something triggering work's virus scan, indexing, or logging that is slowing it down.

 

[SkipVought]

subtotal functions won't work in the other views/summaries/user processes.

What does that mean?

You might take a look at using the Structured Table feature. A whole bunch of functionality is built into this 'new' (as of Excel 2007) feature, like AUTOMATIC subtotaling (sum, count, min, max etc) when you use the AutoFilter.

When you paste a formula into a Structured Table, the formula AUTOMATICALLY propagates to ALL rows, AND when you ADD a new row at the bottom of your Structured Table, ALL your formulas will automatically propagate to this new row!

Structured Tables makes it much easier to write formulas, especially when the Structured Table that you might be referencing may be on another sheet, as EACH structured Table/Fields will appear in the formula Intellisense prompts -- REALLY NEAT STUFF!!!

I would not recommend the paste technique that you have described!!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[firefytr]

Have you ever thought of using a pivot table with this data source? I don't know how your data is structured though, which could be problemmatic (depending, of course). I'm a big fan of using tables, as Skip suggested. The structured table referencing is very nice. Here is some good information relating to that topic...

http://office.microsoft.com/en-us/e...references-with-excel-tables-HA010155686.aspx

There are some shortcomings, but they're fairly well-rounded out. One of the hardest things is a running total (which I blogged about here:

http://excelandaccess.wordpress.com/2012/07/12/running-total-in-an-excel-table/).

I know it may sound funny, but make sure your display driver is up to date with the latest software. Autofilter is display-intensive. It is possible that your graphics card doesn't have the memory capacity to render fully, and, as I believe how the Excel engine is structured, with Autofilter it can delay rendering. I would test on some other machines.

Another option to try would be to disable all add-ins and try running it again. You'll need to close and re-open Excel after you've disabled them all. If it works better, re-enable them one-by-one, closing and opening Excel between each one, then re-testing. Once it slows down you've found the culprit (if this is the problem).

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP?
- Leonardo da Vinci

 

[mrstaggart]

@SkipVought: I'll look at the Structured Table functions to see if it will work with this.

@Firefytr: I have and the Pivot table doesn't meet the need - this is a model of client activity and the details are important. Actually, this data becomes a pivot table on another tab to summarize when needed. I use the YES as a page flag to not double count when only totals are needed and select not "YES" to get order summaries.


I have run into this issue a lot only after converting to 2010, so I am convinced it is not the formulas but the pc/Excel 2010/network services (it happened today while converting text to numbers - 33,000 lines not used in formuals anywhere, took 25 min). I'll keep hunting through settings or track what's running to figure this out.

Thanks for the new ideas, though, for other future files!