SOLVED

Deleted
Not applicable

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

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

176 Replies

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

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,

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

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!

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

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

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

can you share your sample file?

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

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

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

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!

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

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.

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

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

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

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

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

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

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

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.
Thank you so much, both for your speed and availability.
Have a good one!

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

You are welcome!
I am glad the formula worked for you.

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

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

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

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

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

@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

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

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!