SOLVED

How to count and sum "Condtional formatting" cells by color in Excel 2010?

Deleted
Not applicable

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/ )

 
My question is: Is there any functional code (or other aproach) which can count and sum all cells by "Conditional formatted" colors, on one sheet, and that the generated result is linked through common formulas? That can be updated/refreshed/code run during the work process as a complete overview of colors of a matrix (and not by picking every color every time as I mentioned).
 
Thanks in advance!

 
181 Replies

@Jamil Mohammad 

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.

Hi Steve,
I assume that your question is already answered by Hans in another thread. if not, I can look at your file.

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!

Jamil, i have followed most of this thread and applied code and formulas to my spreadsheet but still have #VALUE errors. I read somewhere early on that the range in the UDF and in the CF need to be the same but i cannot see any range addresses in the VB text
can you share your sample file?

@Jamil Mohammad Well thanks but this doesnt work for me, see attached

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!

@GiadaBellan 

 

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.

@ajt8888

I looked at the file. the same issue as other users. the applied range of the conditional formatting and the range used inside UDF are not consistent. and also the CF rules are not set using formula.

@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!

@GiadaBellan

 

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. 

Hey Jamil, this is everything I wanted to do!
I will have some studying to do as I do not understand the formula at all, but google will be my friend there. :D
Thank you so much, both for your speed and availability.
Have a good one!
You are welcome!
I am glad the formula worked for you.

@Jamil Mohammad 

 

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 

 

I meant that when I copy the cell B1 to B2, How can I use a function to keep the same formula as B1, without changes.
Thanks a lot!
Joyan

@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

@Jamil Mohammad 

 

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!

HI Jamil,

This actually helped me but I am getting an error because it is counting more of the red cells that what I have on the range. I am not sure how to figure it out. Please help

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!

@kimmarian 

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.