Issue with SumByColor Function in Excel

Question

Issue with SumByColor Function in Excel

The "=@SumByColor(xx,(yn:ym))" function has been functioning correctly for the past 3-5 years, but it has suddenly stopped working. There was no custom macro created in VBA for this function; it has always worked as is. What could have caused this issue?

Thank you for any advice.

Answer 1

Hi,

Here are a few steps you might want to try.

• SumByColor is not a built-in Excel function. Excel does not have a native SUMBYCOLOR function at all. The only way it shows up is through a custom VBA User Defined Function, or maybe a third-party add-in like ASAP Utilities or Kutools, or even some Power Query workaround that someone set up for you. If you did not write any VBA code yourself, then it probably came from an add-in on your machine. Or it could be in a hidden workbook or macro template, like Personal.xlsb.
• Why it suddenly stopped working. A few likely causes come to mind. Office or Excel updates can disable or break old add-ins and macros sometimes. The add-in might be disabled or removed. Check under File, then Options, then Add-ins. If it is in Disabled Items, just re-enable it. Macro settings could have reset. If security got tighter, VBA functions might not run automatically anymore. You might have moved the file to another machine without the add-in. That would make it show as #NAME?. Company IT policy changes happen too. In work settings, they often block unsigned macros or remove third-party add-ins.
• How to confirm the cause. Open Excel and press Alt+F11 to get to the VBA Editor. Look in Modules or ThisWorkbook for a function named SumByColor. If it is missing, then it was from an add-in. Go to File, then Options, then Add-ins, then Manage Excel Add-ins, and hit Go. See if there is an unchecked add-in related to SumByColor or utilities. If you still do not find anything, it was almost certainly from a third-party add-in like Kutools. Try reinstalling that.