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
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
Thank you very much for you kind and very helpful answer.
I will try the explained method to finally resolve my issue.
Thanks and have a great day !
You are most welcome. Thanks for the feedback.
Hi Jamil,
Thank You for posting this solution, I hope that this will also solve my problem With counting the colored cells when using conditinal formatting.
However, so far I am not able to get a number Count, I only get "NO-COLOR". E.g. =CountConditionColorCells(E2;E2:E5) or =CountConditionColorCells(E2:E5;E2) Perhaps I'm completely far out... Could You please let me know how the formula should be written?
Regards,
Julie
I have tried to use the SUM formula here, but keep getting "NO COLOR"...I read through all your posts and tried to troubleshoot, but can't figure out what I'm doing wrong...can you take a look for me?
Hi Michelle,
There is a longer thread on the same UDF, there are multiple things that can cause the return of "no color"
Please read the limitations of the UDF and other comments here https://techcommunity.microsoft.com/t5/Macros-and-VBA/sum-by-color-when-colors-are-set-by-conditional-formatting/td-p/36495
haii i already try this but not successful la.. please help me.
Please look at my attachment and my problem at sheet ZONE_WK25(CountColor) and at column AV10 =countconditioncolorcells($D$10:$AS$10,AV5)
- null null
you asked the question in two places, so i replied here on why the UDF did not work for you.
please see my post here. https://techcommunity.microsoft.com/t5/Macros-and-VBA/sum-by-color-when-colors-are-set-by-conditional-formatting/td-p/36495/highlight/true/page/2HI Jamil.
I went through all the discussion here and yet I can make my excel sheet to work with this code, I just get 0.
Can you have a look at the data.
I am attaching the macro-enable sheet and the one without it.
I want to have a summary table of how many people have the training completed, about to expired, experied, and never been taken.
RGS.
the code doesnt work with my file..can you help me?
- Hi Haziyatul,
I checked your file. The range where the conditional formatting is applied and the range where the UDF uses have to be the same and they are not the same in your excel file.
CF uses $M$9:$EA$56 and UDF uses X9:Y13
so to make this work, you need to either exactly set CF for each of the vertical lines.
however, you would not need to use the UDF for this, you can simply achieve the same result by using the SUMIF or SUMPRODUCT formula. you can see the examples in my earlier posts in the same thread.Jamil Hi Jamil, I thank you for your work and effort to help.
But, it seems your code is not working here because I need to see conditional formatting changing colors at columns by the values of the cells and I need to count those color changes at rows direction, after that.
Do you think that it can be done?
Thank you Jamil. but this function doesn't work in case of "Top 10 Item" conditional formatting. Do you have any idea for my problem?
I have tried your formulas and they do not work. I have been stuck on this for 3 weeks now and I'm trying to count my cells as they turn certain colors from Conditional Formatting rules I put in place for my drop down menu's. If you would be able to assist me that would be wonderful because I am getting tired of watching and reading videos on how it wont work for me formulas.
Matthew
- Hi Mathew,
I looked at your file. You have set up the conditional formatting using the = cell value option .
The UDF only works if the conditional formatting is set using formulas not through the built-in option of conditional formatting.
You can read about it here
https://techcommunity.microsoft.com/t5/Excel/sum-by-color-when-colors-are-set-by-conditional-formatting/td-p/36495
What you are trying to do is possible, but you have set many conditional formatting rules.
I can help you if you give me the list of the text that are for green color and the list of the texts that are for red color.
these are all of the list and if you can give me which one of them are for green and which one for red. I can build the formula for you.
Acceptable Use Policy
Access Control List
Access Point
Address Resolution Protocol
Address Space Layout Randomization
Advanced Encryption Standard
Advanced Encryption Standard 256 bit
Advanced Persistent Threat
Annualized Loss Expectancy
Annualized Rate of Occurrence
Anti-virus
Application Programming Interface
Application Service Provider
Attribute-based Access Control
Authentication Header
Authentication-Authorization-Accounting
Basic Input/Output System
Bridge Protocol Data Unit
Bring Your Own Device
Business Availability Center
Business Continuity Planning
Business Impact Analysis
Business Partners Agreement
Certificate
Certificate Authority
Challenge Handshake Authentication Protocol
Chief Information Officer
Cipher Block Chaining
Cipher Feedback
Closed-Circuit TeleVision
Common Access Card
Completely Automated Public Turing Test to Tell Computers and Humans Apart
Computer Emergency Response Team
Computer Incident Response Team
Content Management System
Contingency Planning
Continuity of Operations Plan
Controller Area Network
Corporate Owned-Personally Enabled
Corrective Action Report
Counter-Mode/CBC-Mac Protocol
Cyclical Redundancy Check
Triple Digital Encryption StandardThese are all the acronyms I am trying to achieve on my spreadsheet, thank you for your assistance
Matthew
I want to count the number of yellow boxes as in the file below and output in the column "count color yellow"
Thank you so much!
- Hi hiepkt12
you have posted the same question in another thread as well. I have replied to you in there.
Hello,
I have try your VBA formula for sum by conditional formatting color, however formula return "0" result when data are in negative. I did conditionally formatted negative data by using ABS formula. I have staff attendance record which shows short hours by negative sign and excess hours by positive numbers. now I need to sum all short hours and excess hours first by highlighting through conditional format and then sum up those hours.
would you please help me to achieve this.
- I did not see any conditional formatting in your file.
so, I did not understand what were you trying to achieve. If you want to sum the total of cells that are negative then you can use lets say for Column A as an example formula is =SUMIF(A2:A40,"<0") this will sum all values in cells that are negative. but if you want to count cells that have negative values then you can use =COUNTIF(A2:A40,"<0")
and to sum the positive numbers =SUMIF(A2:A40,">0") and to count the positive numbers =COUNTIF(A2:A40,">0")Hi Jamil,
Thank you for response.
Sorry I did not make clear what I'm looking for. I do not want all negative to be sum only those cell value which is greater than -1.5 and less than -10.00 should be colored and sum and similarly all the cell value greater than positive 1.5 are to colored and sum.
I have done conditional formatting in attached file and try to apply your formula but its not working. Please help me out with this.
Thanks again.
Hi There
I know very little about VBA's.
I've got a very large s/sheet with data that I've just inherited. I need to add this months data to the master sheet (hystoric) and I'm trying to make the process easier and less time consuming (previously done manually). It's got columns with Customer Name, Address, Territory, Serial Number, Device Name, etc (columns A to O) and then approx 6900 rows of data. In my Serial Number column I've got a conditional format (column H) (format values where this formula is true =countif(if serial number is different to last month) - fill - red) and then I've got a conditional format in column K (format values where device name is different to last month - fill - blue). I've also got subtotals - count per territory number.
What I'm trying to achieve is to count all the non-coloured Serial Numbers (cells in H) and all the change in device name (non-coloured cells in K (less non-coloured cells in H)).
I've inserted your UDF in VBA. In H373 (first change in territory) I've typed =COUNTConditionColorCells and hit Enter and get the "#Value!" error. Not sure what I'm doing wrong, please help.
I'm trying this formula and I keep getting 0's. I'm not sure what I'm doing wrong and I'm wondering if you can help. See my data.
If you read my earlier posts, you will see that I mentioned UDF only works if CF is set using formula not build it conditions.
I changed the CF in the attached file as an example and see it works.
Hi Jamil, trying to use the count feature on conditional formatted cells, but the count is not accurate. My range of cells are restricted to 1 row at a time of cell highlighted in blue, but it appears to give the same result, regardless of the range provided. The spreadsheet has been attached. Any thought?
