How to count and sum "Condtional formatting" cells by color in Excel 2010?

GiadaBellan 

I looked at the file.  it does not work because of two reasons, if you look at my earlier posts. the UDF has two limitations.  A) the range you used in the UDF N4:AK4  and the Conditional formatting you used in for this range is not the same. there are multiple conditional formatting with different dimensions in in this range.  B) the formulas that set the conditions of UDF are not set using formula but the "Format Cells that only contain"  the UDF only works if formulas are set using "Use a formula to determine which cell to format".   there are multiple conditions. I tried to understand the logic. perhaps built-in formula can be used instead of the UDF to return the same result you are looking for.

AECRoy 

the CF range was not consistent with the range used in the UDF. I modified in the CF applied range and it works now. please see attached.

Afsarulbd 

It does not work because your conditional formatting "Applied range" is not the same as the range used in the UDF.

scott_terry87 

Thanks. 

I looked at the file you shared.   the reason you are getting NAME error, because you have many UDFs in the workbook, but none of them were the one I shared in this thread. which is this one 

Function COUNTConditionColorCells(CellsRange As Range, ColorRng As Range)
Application.Volatile
Dim Bambo As Boolean
Dim dbw As String
Dim CFCELL As Range
Dim CF1 As Single
Dim CF2 As Double
Dim CF3 As Long
Bambo = False
For CF1 = 1 To CellsRange.FormatConditions.Count
If CellsRange.FormatConditions(CF1).Font.Color = ColorRng.Font.Color Then
Bambo = True
Exit For
End If
Next CF1
CF2 = 0
CF3 = 0
If Bambo = True Then
For Each CFCELL In CellsRange
dbw = CFCELL.FormatConditions(CF1).Formula1
dbw = Application.ConvertFormula(dbw, xlA1, xlR1C1)
dbw = Application.ConvertFormula(dbw, xlR1C1, xlA1, , ActiveCell.Resize(CellsRange.Rows.Count, CellsRange.Columns.Count).Cells(CF3 + 1))
If Evaluate(dbw) = True Then CF2 = CF2 + 1
CF3 = CF3 + 1
Next CFCELL
Else
COUNTConditionColorCells = "NO-COLOR"
Exit Function
End If
COUNTConditionColorCells = CF2
End Function

 I noticed overlapped conditional formatting rules, as well as inconsistency of ranges used in UDF versus ranges used in the conditional formatting.  So, even if you place the above UDF in the workbook. it will not work, because of the aforementioned issue of inconsistent range and overlapping CF.

I thought maybe you are overkilling this by using UDF, whereas the solution you are looking for can be achieved by using built in functions.

I rebuild the CF and placed some formulas in the cells O to QG and formatted those cells as custom format ;;; which shows nothing, while there is a value in it. 

then applied a CF that if any of those cells hold value of 1 then show green. also those cells has nested IF formula to return 1 if the column E is not blank and is greater than row 14.  then in column G, I have used a COUNTIF formula to count if row of O to QG hold a value of 1.

Please see attached and let me know if it works.