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
Hi Alin,
I checked one of your file and it is clear that you did not use the option of CF "- Use a formula to determine which cells to format" the UDF only works if you used formula to determine which cells to format.
I have used one of the worksheets that i found on this topic, on which the count by color worked, and i adapted it for my example.
i have used a formula in conditional formatting in order to highlight all the cells that contain "TEXT".
But somehow it is showing only a count of three for all the three rows that i used for example.
I missed something for sure.
Attached the worksheet.
Hi Chris,
What you are after is possible and this UDF can do that.
If you read my earlier messages in this thread, you will notice that I mentioned that for this UDF to work, the conditional formatting rule should be creating using the option "use a formula to determine which cell to format"
to demonstrate, I have changed the rules of conditional formatting for range P9-18 and replaced it with formula. I replaced the rule, between 0 to 9 with =AND(ISNUMBER(P9),LEN(P9)=1) and also the other two colors as you can see it in the attached file.
then I placed the UDF as an example in the cell C51 which correctly returns the value. given this example, you can replicate this for other ranges/cells.
plz see attached file.
Hi Jamil, thanks for your quick response.
I am trying to count the cells in column P in each different section and total these to a specific cell- these have been condition formatted to change colour depending on the values that are put into them, which are summed by the Moulds & Yeasts Columns (Q&R)- for example, I want the total of green cells within P9-18 to be totalled inside C51 and then the orange and red cells from P9-18 in C52 and C53, but I want C51, 52, and 53 to be able to update when changes occur in P9-18 and the colours change. I have managed to add a VBA code where the totals can recognise the colours but I cannot get them to update when the colours change from their value.
I have attached a copy of the workbook without the macro enabled as it was the only way the site would allow me to send it. hope this helps.
Many thanks for your help!
Kind regards,
Chris- 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.
- JamilHi Jamil I'm at my wits end on how to do this. I have a spreadsheet that I use to monitor a football pool. I have 8 columns conditionally formatted to show highest value, now I'm trying to find a way to count any formatted high values in the rows that cross these columns. I've worked through just about everything previously mentioned in this thread and still cannot come up with the formula needed to count the high values. Any help would be hugely appreciated. I've attached a portion of the spreadsheet I'm working on. Edit: After further researching this issue, and reading the rest of the posts in this conversation, as I should have, it appears that what I'm trying to do isn't possible. Hard to believe that something as simple as counting colored cells across rows that are formatted by conditional formatting in columns, can't be done. I've looked for other options to do this and can't find anything that suits me. Is there a way this can be done?
Hi Mr Jamil
I would like to thank you for this formula. It works great! However, I have a query. Please see attached file. I have a set of KPI’s for disciplines to meet. When I use your formula to count, ‘No of disciplines met by KPI’, it works. However, when I used your formula to count, ‘No of KPI's met by Discipline’, it doesn’t work. May I know what could be wrong? Your help is greatly appreciated :)
Thanks for your kind words.
I looked at the file you attached.
If you read my earlier posts, I have mentioned to other users with similar issues as stated below
- Your CF range does not use only formula, for UDF to work, CF needs to be set up only using formula and not built-in options given for example you use the option of "Format cells that contain"
- UDF range uses the whole A to M columns, whereas your CF ranges are completely different. for example some of them starts from row one and some in row 2 , another CF uses double column applied range $H$2:$H$31,$J$2:$J$31
- For UDF to work, the range in UDF and ranges in CF must match and the CFs needs to be set using only formula.
Hope this helps.
I am so VERY impressed with your expertise in MS Excel. I'm trying to do this very same task of counting cells based on CF color. I've attempted your code but get "NO-COLOR" as the output. I've attached a dummy file so you can see what's going on.
The only thing I can figure is that it's a different version of excel or I have a setting blocked somewhere.
Thank you so much for your kind consideration and help.
- try to see if you have already a CF set for that cell with a different formula. then it will work. so you will need to use a workaround. use countblank function instead.
I found this code to be used in my file but I want to switch from "Sub" to "Function". That is, I want the result of the yellow box displayed in the column count color yellow.
Help me!!
Thank you very much!!
Sub DisplayFormatCount()'Updateby20150305Dim Rng As RangeDim CountRange As RangeDim ColorRange As RangeDim xBackColor As LongDim xFontColor As LongOn Error Resume NextxTitleId = "KutoolsforExcel"Set CountRange = Application.SelectionSet CountRange = Application.InputBox("Count Range :", xTitleId, CountRange.Address, Type: = 8)Set ColorRange = Application.InputBox("Color Range(single cell):", xTitleId, Type: = 8)Set ColorRange = ColorRange.Range("A1")xReturn = 0For Each Rng In CountRangeqqq = Rng.Valuexxx = Rng.DisplayFormat.Interior.ColorIf Rng.DisplayFormat.Interior.Color = ColorRange.DisplayFormat.Interior.Color ThenxBackColor = xBackColor + 1End IfIf Rng.DisplayFormat.Font.Color = ColorRange.DisplayFormat.Font.Color ThenxFontColor = xFontColor + 1End IfNextMsgBox "BackColor is " & xBackColor & Chr(10) & "FontColor is " & xFontColorEnd Sub- You are most welcome.
Yes. you can simply add another condition that cell ="" then select "No color" in the fill That worked perfectly! Thank you so very very much! One more question...Is there any way to have the cells that have no data in them remain uncolored? I was playing with the formulas but as I'm sure you've figured out by now I'm not the best at them. Everything else is working beautifully and I am grateful for what I've got but... :)
thanks for your kind words.
I saw your workbook and there are many issues with the CF formulas you set.
Double condition set for a single cell with contradicts each other.
so example in the cell E28 there are two formulas in CF and both of them contradicts each other.
=IF(E28>D28,"true","pass")
=IF(E28<C28,"True","pass")another issue is that you set IF formulas to return test in CF which is not correct way to set formula.
for example if you want a condition to turn red if certain condition is met then you can simply use equal sign and there is no need for IF function, in fact IF function ruins it.
for example if you want to turn cell E28 to red if its value is greater than D28 then you can simply use =E28>D28 simply as this. CF will return True if condition is met and false if it is not met.
Please see attached your workbook.
I have added two more UDFs so that you can debug and find the problem.
The first UDF return the formula used in the CF for that cell
Function CondFormulaformula(myCell, Optional cond As Long = 1) As String
CondFormulaformula = myCell.FormatConditions(cond).Formula1
End Functionthis second formula evaluates the formula used in CF and returns the evaluated value.
Function CondFormula(myCell, Optional cond As Long = 1) As String
CondFormula = Application.Evaluate(myCell.FormatConditions(cond).Formula1)
End Functionyou can see in the column H which i highlighted in yellow. the evaluated CF from your formulas and they are not consistent. with the help of two addtional UDFs you can debug your CF formulas and get the correct result.
Hope this helps.
Good morning Jamil! This thread has been SO helpful! I really appreciate it. Of course, since I'm joining I'm having trouble lol.
I added the UDF and made sure that the CF I'm using is based on formulas however I can't seem to get the results to be stable.
This is a fairly simple application and I'm feeling pretty dumb right now as I'm sure whatever it is I'm doing wrong is an easy fix.
Would you mind terribly taking a look? I tried a couple different formulas in the CF but even the straight-forward ones don't always end in the correct result. Sometimes the count is working and sometimes it isn't.
Any help you can give me would be much appreciated. This is making me nuts lol.
Thanks!
- It should work. i suspect there must be an issue with the way CF range is set.
if you upload a dummy sample, i can take a look.
