Forum Discussion

heylookitsme's avatar
heylookitsme
Iron Contributor
Dec 15, 2020

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 th...
excel
Formulas and Functions
heylookitsme's avatar
heylookitsme
Iron Contributor
Jan 26, 2021

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's avatar
      heylookitsme
      Iron Contributor
      Jan 27, 2021
      Microsoft 365 Apps for Enterprise
      Version 2008 (Build 13127.21064)
      Semi-Annual Enterprise Channel
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jan 27, 2021

        heylookitsme 

        Here results shall be close to JKPieterse  test, other words no dramatical difference in performance between table and range.

Resources