09-07-2019 03:25 PM - edited 09-09-2019 10:34 AM
09-19-2019 12:22 AM
Hi! I came across to this thread coz i was looking for an answer to my problem. And found that you posted one for "
However, what I want is to count all cells with red font color in a range but the FONT COLOR was based on the conditional formatting.
Example: range is A1:A1000 then the formatting I made was to turn DUPLICATE ENTRIES into a RED FONT COLOR. regardless of the text content. if apple and banana were duplicated twice. the result should give me NOT 2 BUT 4. Please help me.
Thanks in advance!
09-26-2019 11:58 AM
Hi @Jamil Mohammad,
Could you please look into a file attached? I am trying to count CF colored cells in "SJA racks" tab.
Getting "0" all the time. I ahve tried GetColorCount and COUNTConditionColorCells = same result "0".
What am I missing?
Thanks in advance for looking into this.
09-28-2019 05:43 PM
No need to overkill it with UDF. you can use built-in Excel formula to get what you need.
simply put this formula in M6 cell of SJA racks sheet and copy it down up to cell M51.
see also in the attached
09-28-2019 05:51 PM
09-28-2019 06:37 PM
you do not need UDF for this, you can accomplish the same with this formula
also see it in the attached workbook.
09-29-2019 11:51 AM
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!!
10-02-2019 02:23 AM
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 ...
2. The count conditional formatting cell per color isn't working (O5)
Thanks for your support
10-02-2019 04:48 AM
I have revised the formula to take care of the blanks.
please see attached.
10-02-2019 05:13 AM
Hi, 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.
10-02-2019 07:57 AM
Once again thank you Jamil! Your help has been invaluable. The changes you've made work perfectly. I've attached a copy of the spreadsheet end result, in case you were curious. Also for anyone who might find themselves with the same issues.
Many thanks to you my friend!!
10-04-2019 04:03 AM
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.
11-04-2019 04:27 AM
You can use this formula =SUMPRODUCT(1-(COUNTIFS($C$42:$G$10010,C5:L5)>0))
please see attached file.