Xlookup & Sumifs Slowing Down Excel File

[jewkes6000]

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).

Google Drive: Sign-in

Access Google Drive with a Google account (for personal use) or Google Workspace account (for business use).

drive.google.com

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

 

[AhoyNC]

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.

 

[AhoyNC]

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]

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?

 

[AhoyNC]

Excel will stop at the TRUE statement.