10-12-2017 02:29 PM
10-12-2017 02:29 PM
I installed few add-ins for counting "Conditional formatted" cells but all are generating an instant (one-time) numeric result by activating that command.
Also, I tried a code which gives an instant counting of 1 picked color without a total sum of all other colors -in my case 12 colors in a matrix. ("How to use the code to count colored cells and sum their values" - https://www.ablebits.com/office-addins-blog/2013/12/12/count-sum-by-color-excel/ )
12-04-2017 03:24 PM
12-04-2017 08:30 PM - edited 12-04-2017 08:31 PM
02-26-2018 07:40 AM
SolutionI 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
03-05-2018 08:58 AM
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 !
03-05-2018 12:49 PM
You are most welcome. Thanks for the feedback.
03-19-2018 02:08 AM - edited 03-19-2018 02:14 AM
03-19-2018 02:08 AM - edited 03-19-2018 02:14 AM
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
03-19-2018 03:05 AM
Hi Julie,
please see attached workbook sample that shows how you can use the UDF.
please post back, if you have any question.
03-19-2018 03:26 AM
Hi Jamil,
Not sure why it is not working on my computer, because after pressing "edit" on Your document, it also got error value "#Name?", and You can see on my document Attached how it looks with error #VALUE! I wasn't able to attach the Excel file With Makro enabled, but have of course had the makro enabled file so far.
Great if you can advice.
03-19-2018 03:44 AM - edited 03-19-2018 03:45 AM
03-19-2018 03:44 AM - edited 03-19-2018 03:45 AM
Hi Julie,
I checked your file.
it was not working because A) you need to enable macros in order to be able to use the UDF.
B) the sample file you upload, you had only cells for criteria colored, but there wasn't any conditional formatting set in your workbook, that's why It did not work.
I have recorded a video that shows how you can set up your conditional formatting.
please download the attached video and watch it. also i have attached your workbook with conditional formatting set in it.
03-19-2018 08:14 AM
03-19-2018 09:04 AM
05-22-2018 01:57 PM
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?
05-23-2018 05:31 AM
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-conditiona...
06-25-2018 06:04 PM
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)
06-27-2018 02:06 AM
08-12-2018 12:45 AM
the code doesnt work with my file..can you help me?
08-13-2018 04:51 AM
09-02-2018 05:30 AM
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?
09-02-2018 05:53 AM
09-02-2018 06:25 AM
Bad luck!
Is there any solution for my problem?
09-02-2018 10:43 AM
09-02-2018 09:47 PM
How can i open a new question?
09-03-2018 06:19 AM
09-05-2018 06:06 AM
Thank you very much.
09-29-2018 09:45 PM
Hello.. can you solve the problem for attached file?
I want to count the specific text colored by conditional formatting column wise. Conditional formatting done by formula.
10-08-2018 11:59 AM
10-14-2018 07:33 PM
you have a new question, so the experts would likely to answer your question over there.
10-22-2018 03:38 AM
Hi Jamil,
I had a similar issue to Julie.
I did exactly what you said and the document worked.
Since saving, closing the document and today opening it the formula doesn't seem to work when calculating the colored cells.
I get the '#NAME' error.
Any assistance you can offer would be appreciated...?
Document attached.
10-22-2018 03:52 AM
10-22-2018 03:07 PM
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
10-23-2018 08:18 AM
10-23-2018 09:41 AM
These are all the acronyms I am trying to achieve on my spreadsheet, thank you for your assistance
Matthew
10-27-2018 03:25 PM
12-13-2018 05:51 PM
Hi Jamil
I stumbled upon your code as I am using conditional formatting formula to highlights my cell. Now I want to count the highlighted cells.
Similar to Julie, the VBA returns "no color" instead of the numbers. I have done everything that I can possibly can.
I suspect it doesn't work because I use this formula in my conditional formatting =if(isblank($K$1),0,(search($K$1,A5))). A5 is where my data starts. K1 is where when someone type a text and that text is in the data, it will highlight those texts. Attached is an example (which didn't quite work but you'll get the idea).
Thanks for your help.
12-19-2018 08:24 AM
hi 7 Heven,
You have used Search function and also whole column reference A:A which is not compatible with the UDF. I have modified the formula and range in the attached example and the UDF works.
12-20-2018 05:51 PM
Hi Jamil
I can't believe my luck that you actually replied to my query! Thank you very much!
If I modified the formula as per your suggestion and remove the search function, it doesn't work on my master spreadsheet. Is there a way to modify the UDF to make it work with search function?
Many thanks again.
01-03-2019 05:39 PM
Hi Jamil I was able to successfully use your code to count my conditionally formatted cells but my data changes regularly and the function doesn't automatically refresh. In order for it to pick up the changes i have to refresh the cell with F2 and then enter, any solution for that?
01-04-2019 10:13 AM
01-04-2019 10:15 AM
01-11-2019 04:09 AM
Hi Jamil,
I have quite a particular situation regarding the count of CF cells, apart from what i saw exemplified in this topic. (i have tested all the examples in this topic for my application and didn't seemed to work)
Basically I have a worksheet that automatically color some cells using Conditional Formatting (some CF colored cells contain text, and some others CF colored cells are blank)
I have attached a test sheet with the structure.
Well to mention is the fact that in column "H" i will have to count the CF cells for maybe thousands of rows and to be able to see the count for each row in part.
Thank you in advance,
Alin
01-11-2019 04:09 AM
Hi Jamil,
I have quite a particular situation regarding the count of CF cells, apart from what i saw exemplified in this topic. (i have tested all the examples in this topic for my application and didn't seemed to work)
Basically I have a worksheet that automatically color some cells using Conditional Formatting (some CF colored cells contain text, and some others CF colored cells are blank)
I have attached a test sheet with the structure.
Well to mention is the fact that in column "H" i will have to count the CF cells for maybe thousands of rows and to be able to see the count for each row in part.
Thank you in advance,
Alin
01-11-2019 04:24 AM
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.
01-11-2019 04:43 AM
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.
01-11-2019 06:02 AM - edited 01-11-2019 06:03 AM
hi Alin,
I looked at your file. The issue was that you had CF applied range A10 to A10:E12 while your UDF were using for each row. the UDF range and the CF range should match, that is when it will work.
I have attached the workbook and also i have attached a recorded video on how to use the paintbrush to apply CF to the other cells without the hassle of recreating CF for each row.
01-11-2019 06:26 AM
thank you so much for this Jamil
I got it finally :)
I wish you all the best
01-15-2019 04:52 AM
Hi Jamil,
Please check my attached file which i want to count the colored CF cells by row. Problem is there are different formulas in each column, if i want to count the result by rows, i get an error. If i count by column wise it got results. Please help so i can count it by rows.
Thanks in advance.
01-15-2019 01:01 PM
01-18-2019 02:13 AM
Hi Jamil,
The example that you helped me with worked like a charm but only for one conditional format formula applied for a range of cells.
Now i have a case in which i want to count all the CF cells in a row, but with multiple CF formulas.
I followed all the steps that you suggested (i hope i didn't missed something out).
I have attached the worksheet.
Thank you in advance
Alin