Xlookup & Sumifs Slowing Down Excel File

jewkes6000

Board Regular
Joined
Mar 25, 2020
Messages
60
Office Version
  1. 365
Platform
  1. Windows
I have an excel file which is running painfully slow. I believe the issues are where I have used the Xlookup and Sumifs formulas. Does anyone have any suggestions on how to speed this up? I am open to using visual basic if needed.

You can download the file at the link below. The two tabs where the issue probably lies is in the "Compare Tool" tab and the "Historical Pricing" tab. What I am trying to accomplish is to take the data from the "Historical Pricing" tab and display it in the format shown in the "Compare Tool" tab. Eventually the Compare Tool tab will have more projects (columns L through AE) where I can compare several projects side-by-side. I want to be able to simply change the project name and have all of the data below change based on that project name. The data "below" is coming from the "Historical Pricing" tab. I have used the Xlookup formula to reference the Project, the columns in row 6, and then the rows in column I, and then reference all of those items to the corresponding columns in the Historical pricing tab. The historical pricing tab will continue to grow, which is why I reference the entire columns (I'm guessing this might be one of the problems).


Any help or suggestions is much appreciated. Thank you in advance.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,929
Office Version
  1. 365
Platform
  1. Windows
Don't reference whole columns. Instead of A:A if your data will never go below say row 50,000 use something like A1:A50000. The IFERROR function will look at all 1 million plus rows and it is doing it multiply times with your forumlas.
 
Solution

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,929
Office Version
  1. 365
Platform
  1. Windows
Along with not referencing whole columns, the XLOOKUP function has an argument [if not found]. You should be able to remove the IFERROR function from your formulas and use this argument set to "".
 

jewkes6000

Board Regular
Joined
Mar 25, 2020
Messages
60
Office Version
  1. 365
Platform
  1. Windows
AhoyNC - Thank you so much. That worked wonders!

I had another related question. My worksheet is not complete. I want to take columns L through AE and copy them 9 times, thus making a total of 10 projects to compare against. This will essentially add 6 columns of Xlookup formulas for each project. However, there will be many times where you only want to compare 2 or 3 projects. In those instances, excel will still be calculating all of those formulas unnecessarily. I was thinking of using an IF formula in front of the Xlookup to say that if the cell with the project name is blank (Cell L6), then return blank and if not, then run Xlookup. For instances where the project is blank, will excel stop at the true statement? Or will it sill run the Xlookup even though it doesn't need to and vastly slow down the spread sheet?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,505
Messages
5,832,124
Members
430,111
Latest member
Francis Xavier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top