Dec 15 2020 12:50 PM
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.
Dec 15 2020 01:15 PM
Depends on you Excel version and actual data structure, but did you try something like this?
Jan 26 2021 07:53 AM
@Sergei BaklanWhat 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.
Jan 27 2021 07:23 AM - edited Jan 27 2021 07:26 AM
@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:
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%.
Jan 27 2021 08:19 AM
@heylookitsme , and on which Excel you are?
Jan 27 2021 08:59 AM
Jan 27 2021 12:44 PM
Here results shall be close to @Jan Karel Pieterse test, other words no dramatical difference in performance between table and range.
Jan 27 2021 01:11 PM
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.
Jan 29 2021 08:57 AM
@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.