Forum Discussion
How to count and sum "Condtional formatting" cells by color in Excel 2010?
I came across this post being unanswered. so here is a User Defined Function in VBA
to SUM
use this UDF
Function SumConditionColorCells(CellsRange As Range, ColorRng As Range) 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).Interior.ColorIndex = ColorRng.Interior.ColorIndex 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 + CFCELL.Value CF3 = CF3 + 1 Next CFCELL Else SumConditionColorCells = "NO-COLOR" Exit Function End If SumConditionColorCells = CF2 End Function
if you want to Count instead of SUM then use the below UDF
Function COUNTConditionColorCells(CellsRange As Range, ColorRng As Range) 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).Interior.ColorIndex = ColorRng.Interior.ColorIndex 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
these solutions were provided to the similar question asked by other Excel users and worked for them.
For more detail here is the link for that answer
If you read through my earlier posts. I have clearly mentioned that UDF only works if the conditional formating is set using Formula. you have used formulas and non-formula options to set your conditional formatting. that is the first issue that is causing the UDF not to work.
the second issue is that your are using the whole column references in your CF which is not going to work.
third issue is that your CF is set by column and you are trying to do the count in UDF using row. If you look at the example I illustrated in answer to Alin. you can see that i modified the CR using painbrush to reflect the rows.
so you need to fix the three of the issues i pointed, so that the UDF would work.
- please see in my earlier posts and find the function which is for count based on the font color.
once you found it, then change the object property from font.color to font.bold Hi,
I've been trying to adapt your code to count cells in a range that have fonts that are conditionally formatted (bold-red or bold-green). The cells use a formula to decide the condition. I found this simple function code below but it looks for font color rather than conditional formatted font color. Any help you can provide would be awesome.
Function CountColour(rng As Range, clr As Range)
Application.Volatile
Dim c As Range
For Each c In rng
If c.Font.Color = clr.Font.Color Then
CountColour = CountColour + 1
End If
Next
End FunctionThanks,
R
Thanks for your comments!
I will explain that file:>In a company, workers use the national lottery to play a game (like bingo).
>Each week the lottery has 2 contests (C42:G73) and each contest 5 numbers;> The first worker C5:L32 to complete all his number (per row) will win all the money;
>There is a conditional format to paint as red (when) the worker hit the number;
The person who is controlling this “social enterprise game” counts (cell by cell, without using a form or a macro) the number of whites, to know how many missing numbers each worker has.
I am trying to replace the “manual counting” with your script, we wish to count the missing numbers (white or without conditional format) = like column M.
ThanksHi, I checked your file. the UDF will not work this sort of range. because your conditional range is not within the range.
I also did not understand why your conditional formatting rule starts from C6 while the data is from C5.
I have put a formula in the attached file that shows how you can count the range of cells that meets the criteria within another range. see if you can work that out.
I read all your posts and appreciate all the comments from you and the other users.
But (there is always a but) I can't understand, why I am getting #Value! error.
So I have a couple of questions for you:
1. How I can create an applies to conditional format per row using the format painter button? I watch that in your video, but doesn't work for me:
At row five I created a conditional format =C5:L5
Each time I press enter (OK), it cames to =$C$5:$L$5
And if I use the format painter to copy to the remain rows it will be like this =$C$6:$L$32
When should be like: C6:L6, then C7:L7 ...
How?
2. The count conditional formatting cell per color isn't working (O5)
Thanks for your support
Hi Jamil, this formula works great except when all the cells in an array are blank. If all the cells in an array are blank then they are all equal, so it counts each as the "max". I've been searching for the possibility of using an IF to check if cell is blank, but not finding anything that suits my spreadsheet. How can I get around this?
Thank you again for your assistance in this. I'm not a strong user of Excel so need some help from time to time. And your expertise in invaluable!!
Jeff Cupps
Hi,
you do not need UDF for this, you can accomplish the same with this formula
=SUMPRODUCT((--(B3=MAX($B$3:$B$12))+(C3=MAX($C$3:$C$12))+(D3=MAX($D$3:$D$12))+(E3=MAX($E$3:$E$12))+(F3=MAX($F$3:$F$12))+(G3=MAX($G$3:$G$12))+(H3=MAX($H$3:$H$12))+(I3=MAX($I$3:$I$12))))
also see it in the attached workbook.
