Removing multiple !REF from SUM formulas quickly

Hi and welcome to the forum

If you have REF errors in a formula, it probably means you have deleted rows or columns that were being referenced. So, removing them is not necessarily the problem, finding what is causing the problem is where you need to start

Hi thank you for the warm welcome

Yes the problem is I have to remove the REF values manually because when I receive the workbook from another person, I need to delete certain values (by removing the row) from the list which results in a lot of REF! in the SUM formulas. I'd figure if there is a faster way to do this it would really save my time for other tasks.

To better clarify what I usually have to do :

Transfer a large list of values between 10-15 lines each with its own sub-totals (SUM formula here) that will be eventually tallied at the bottom with a total (the final SUM formula of the list) that takes the values of the SUM formulas from all the subtotals. I have to delete chunks of subtotals which result in a lot of #REF! for the final SUM formula. I remove the REFs manually after that. Hope that is not too confusing.

Have you tried, instead of deleting the rows, just deleting the data IN the rows, and leving blank rows? This will eliminate the REF error, and you can hide the empty rows if needed