Forum Discussion
Nested If Statement using Sum (Need to Optimize)
I am using Sum(IF( in order to sum data based on Column and Row lookup. Except every time I paste the formulas into the sheet it takes 5-10 mins for it to process. 10,000 cells max. What can I use that would provide a much faster outcome? Because no one will use this sheet if it takes that long and three other sheets require it as well.
The formula must look up and match to a row header and then match data to a column. I'm hoping I get an answer today which is why I posted here because everyone here are rock stars.
8 Replies
If it's possible to sort your data, at least by a primary key, then you may be able to use match functions as helpers to narrow the range your sumif(s)/sumproduct function is evaluating. I've used this in the past and, as I recall, made a notable difference.
JMB17I failed to mention that my Table has 450 Columns of Data. That may be the issue. I can't reduce those columns either. Seems like issues such as this occur when a table exceeds 250 columns. I also noticed that file size increases significantly when I pass the 250 column mark.
heylookitsme I can't say I reproduce this. Tables do have some perf hit, but it isn't too bad in the test I did (see test workbook attached).
Two sheets: A sheet with data in a table (10,000 rows) and the sumproduct formula
and an identical sheet with the same data, not in a table.
I ran VBA code doing this for both worksheets:
- Calc on auto
- Run 50 times:
- Filter the table using greater than 50 on the second column
- Unfilter the table
- Calc on manual
- Run 50 times:
- Filter the table using greater than 50 on the second column
- Unfilter the table
I get these results:
Calc RangeFilter TableFIlter auto 1.840 2.068 manual 0.584 0.490 With calculation ON, filtering the table takes longer than filtering the range
With calculation OFF, filtering the table takes less than filtering the range
But the difference isn’t that shocking.
If I delete all formulas and rerun the code I get this:
Even now, there is a difference between having calc on or off, but now filtering the range is quicker in both situations, by about 20%.
Depends on you Excel version and actual data structure, but did you try something like this?
SergeiBaklanWhat I ended up doing was using VBA, creating a separate sheet in the workbook and moving over the columns I needed to calculate to this new sheet. I then complete the calculations in this sheet and copy and paste the data as value back into the original sheet.
I like Excel Table Format for all of the flexibility it gives me. However, It is much faster to calculate data not using an Excel Table. I have found that Excel Tables only use 50% of your PC's Processing power where autofilter data uses up to 100% of your processors. From my understanding this has been a known issue with Excel Tables for a long, long time and I really wish they would fix it. I addressed this by up voting it in Excel Ideas Forum.
heylookitsme , and on which Excel you are?
