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/ )
May 01 2021 02:28 AM
The background colors on cells $D$3:$F$5, a named range called COLORS2, have been applied using Conditional Formatting based on criteria met using values in columns B and C.
For instance, a value of 9 on 29-Apr is assigned a background color of "Green" (Hex #92D050) because it fell between the range of 8.5 and 10.5 per rules of item A.
The other values below and above that range were assigned "Red" (Hex #FF0000).
If either values equaled the range boundaries 8.5 or 10.5, they would be assigned "Orange" (Hex #FFC000), while blanks would not be assigned a background color and therefore should not be counted.
The CF formulas assigned are as follows:
Blank ISBLANK(D8)
Green AND(D8>$B8,D8<$C8)
Orange OR(D8=$B8,D8=$C8)
Red AND(NOT(ISBLANK(D8)),OR(D8<$B8,D8>$C8))
I could not find a clean way to arrive at a COUNT for the expected values for each row (not aggregated) based on the conditions described (see attached sample). Can this be accomplished without VB scripts? If not, please provide one including a step-by-step explanation of how to use it specifically for this type of request.
May 04 2021 07:35 AM
May 05 2021 03:32 AM
Hi @Jamil Mohammad,
Thanks a bunch for your response, and indeed I confirm that Hans provided me a solution that worked for me in this thread.
However, if (and only if) you have time and could suggest yet another alternate solution (perhaps with checking background CF colors), that would be very much appreciated so that I could have more "tools under my belt" for future problems like these.
Thanks again!
May 06 2021 07:58 AM
May 06 2021 10:06 AM
May 18 2021 05:48 AM
@Jamil Mohammad Well thanks but this doesnt work for me, see attached
Jun 17 2021 09:18 AM
Hey there @Jamil Mohammad
Thanks for the fantastic instructions!
I have tried to use the COUNT macro; the point would be to count how many blue cells are in every row.
I have not changed all the rules (they are not all formulas, but the first is) but I am not sure what else is not working.
At least the first four cells should give the result of 1 instead of NO-COLOR, right?
Could you please give it a look?
Thank you so much!
Jun 17 2021 10:20 AM
I looked at the file. it does not work because of two reasons, if you look at my earlier posts. the UDF has two limitations. A) the range you used in the UDF N4:AK4 and the Conditional formatting you used in for this range is not the same. there are multiple conditional formatting with different dimensions in in this range. B) the formulas that set the conditions of UDF are not set using formula but the "Format Cells that only contain" the UDF only works if formulas are set using "Use a formula to determine which cell to format". there are multiple conditions. I tried to understand the logic. perhaps built-in formula can be used instead of the UDF to return the same result you are looking for.
Jun 17 2021 10:29 AM
Jun 17 2021 11:08 AM
@Jamil Mohammadthank you for the swift response!
I did read a few of the posts, but after a few pages my eyes crossed, thank you for taking the time for explaining it anyway.
The tool has the objective of counting how many tasks are running at the same time every day.
Given each date a task with a different 'day duration' value, you can see on the right which ones overlap with each other's dates.
That is why I have so many conditional formatting rules.
My goal was then calculate how many colored cells were there in every row to see the number of tasks running.
I am not sure if I can write a formula that accommodates every condition needed.
I am still relatively new to Excel, and I am open to suggestions if you can envision a better/simpler way to realize this.
Thank you again for your insight!
Jun 17 2021 12:05 PM
Hi again,
Not sure if I fully understood the requirement. based on what I understood. I came up with an array formula.
=SUMPRODUCT(--(($O4:$AK4)<=TRANSPOSE($G$8:$G$30))*($O4:$AK4<>""))+1 I used this formula in Cell L1 and dragged it down.
If you do not have Excel 365, then the formula to be entered using Control + Shift + Enter
Please see attached file.
Jun 17 2021 12:15 PM
Jun 17 2021 12:54 PM
Jul 18 2021 01:37 PM
Hello
Jamil,
Greeting!
I am wondering, How Can I submit my questions.
Wish you can help , below is my question:
How to keep the same referred the cell value when I copy it to the another cell?
In my attached example, I would like to keep B2, B3 are as the same as B1 cell formula.
Thanks a lot!
Joyan
Jul 18 2021 01:39 PM
Aug 18 2021 03:28 AM
@Jamil Mohammad Does this work for cells which have conditional formatting as a result of using/being sourced from xlookup? At the moment I am just getting #VALUE. Thanks
Aug 22 2021 04:21 PM
Hi Jamil,
I need your assistance with this workbook. I have applied the VBA code and followed the steps to the best of my ability. I still get a result of #NAME? in the total.
Can you take a look at this file and let me know where I went wrong? Thank you!
Apr 12 2022 08:32 PM
Apr 30 2022 11:45 AM
Hello @Jamil Mohammad
I've conditionally formatted cells to turn green once the condition is met. that formatting was done using a formula. now I want to count the number of green cells but can't seem to figure it out. I've taken a look at the VBA code provided for "counting" and not "sum". But I only get a return of no color. in the code I see three criteria (CF1, CF2, CF3) but I only have two. Is there a way for me to modify the code?
Thank you!
Apr 30 2022 12:32 PM - edited Apr 30 2022 12:33 PM
It would help if you could attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar. Thanks in advance.