Hi Carl,
Looking at your workbook, there is a mismatch between (applied range to) in the conditional formatting and the UDF.
If you look at your conditional formatting rules, the red color background rule is applied into three separate noncontinuous range $A$1:$A$54 , range $D$1:$M$54 and range $B$1:$C$54
while your User Defined function range input is the whole range A1:M54.
The UDF looks up for the match for ranges used in the conditional formatting and the input range inside the function and if there is no match, it will not output correct result.
So, if you want to work with the UDF you need to use combination of ranges together in UDF exactly as per the conditional formatting range. so if you put this =COUNTConditionColorCells($A$1:$A$54,O2)+COUNTConditionColorCells($D$1:$M$54,O2)+COUNTConditionColorCells($B$1:$C$54,O2)
it will return 5 which is correct.
but if you do not want to use the UDF, then you can acheive the same result by using Excel built-in Functions like this
=SUMPRODUCT(--($A$1:$A$54<=(TODAY()-730)))+SUMPRODUCT(--($D$1:$M$54
<=(TODAY()-730)))+SUMPRODUCT(--($B$1:$C$54<=TODAY()-365))
The above formula using built-in Excel function will result the same, it returns 5.
Hope it helps.
