Count By Color

[DPChristman]

I have a formula that counts all cells with a specific color fill.

The formula is this:

=@CountCellsByColor($V$2:$V$54,$P4)

$V$2:$V$54 is the range of rows to be counted by color.

$P4 is the cell address with the matching color.

This formula has worked successfully for years.

Suddenly, today, I am getting a #NAME? error.

Any thoughts as to what the issue might be, or a better formula to get the results?

Thanks in advance.

 

[Joe4]

That means it is not recognizing the function.
"CountCellsByColor" is not a native Excel function. It is probably either a User Defined Function (UDF) that was added in VBA, or part of some Add-In.
Did you verify that you have this function defined in your VBA or part of an add-in you are using.

 

[DPChristman]

That means it is not recognizing the function.
"CountCellsByColor" is not a native Excel function. It is probably either a User Defined Function (UDF) that was added in VBA, or part of some Add-In.
Did you verify that you have this function defined in your VBA or part of an add-in you are using.

Not sure how to confirm that, but this formula has worked for years. To add to the confusion, this is a weekly file that I create, and when I pulled up previous versions of the file that I have created, the formula worked they now show the same error. I have made no changes to any of these files.

 

[Joe4]

Check to see if your VBA code has been disabled. Sometimes updates can disable them, by default.
If Macros/VBA has been disabled, it won't be able use those functions in VBA.
See here: Enable or disable macros in Microsoft 365 files - Microsoft Support

 

[DPChristman]

Check to see if your VBA code has been disabled. Sometimes updates can disable them, by default.
If Macros/VBA has been disabled, it won't be able use those functions in VBA.
See here: Enable or disable macros in Microsoft 365 files - Microsoft Support

Ok, I did that and I get this error message. I guess I have to go through my IT department.

Thanks

 

[Joe4]

Yeah, you will have to get those settings changes if you still want to be able to use that function in your workbooks.

 

[RoryA]

That message has nothing to do with macro settings. If they aren't greyed out, and they aren't in your picture, you should be able to edit them.

The message relates to this setting: