Oct 12 2017 02:29 PM
Oct 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/ )
Jan 04 2019 10:15 AM
Jan 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
Jan 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
Jan 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.
Jan 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.
Jan 11 2019 06:02 AM - edited Jan 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.
Jan 11 2019 06:26 AM
thank you so much for this Jamil
I got it finally :)
I wish you all the best
Jan 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.
Jan 15 2019 01:01 PM
Jan 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
Jan 18 2019 03:46 PM
Good evening Mr. Jamil Mohammad,
I read your post and downloaded it. It works well.
But I would have a question of how to solve each line separately for each color.
Thank you for your reply and help in advance, a nice evening.
Example:
Jan 21 2019 03:53 AM
Hi Norbi,
I will respond to your post due to the fact that i had the same challenge as you, and Jamil showed me the way around this.
If you check the previous post in which Jamil replied to me you will see that the CF range need to be exactly the row for which you need to make the count. you cannot use a range like A3:G16 because it won't work. The correct way to do it is to set like this: A3:G3. - for the first row.
And afterwards you need to use the format painter and to paste the CF formulas row by row. As a tip, if you want to make multiple formats with the format painter you can double click the format painter and it will remain active for all the pastes.
Jamil also made a video with this step in the post he replied to me.
i have attached the modified workbook
i hope it helps
Alin
Jan 27 2019 12:48 AM
Hello Alin20
thank you very much for your answer managed to solve my problem. I realized that there were a lot of conditional formatting and could not count the colors correctly.
Thank you very much for your help again.
Hi,
norbi
Feb 05 2019 09:33 PM
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
Feb 13 2019 01:31 PM
Hi Alin_20.
You did not properly set your conditional formatting in your file, that is why it was not working.
I set the conditional formatting in the attached file correctly and it works now.
plz see attached.
Feb 18 2019 12:32 PM
When I use the formula, it works rows in the column except for the 2nd row of data. It does not count that row. Has anyone hit this issue or found a way around it? It correctly counts all of the other rows so I know it is looking at the condition formatting.
Feb 19 2019 11:34 AM
Mar 29 2019 05:31 AM
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!
Mar 29 2019 08:23 AM
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 Function
this 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 Function
you 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.
Apr 01 2019 01:53 PM
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... :)