SOLVED

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

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

 
147 Replies
Highlighted
@Jamil MohammadHi Jamil I'm at my wits end on how to do this. I have a spreadsheet that I use to monitor a football pool. I have 8 columns conditionally formatted to show highest value, now I'm trying to find a way to count any formatted high values in the rows that cross these columns. I've worked through just about everything previously mentioned in this thread and still cannot come up with the formula needed to count the high values. Any help would be hugely appreciated. I've attached a portion of the spreadsheet I'm working on. Edit: After further researching this issue, and reading the rest of the posts in this conversation, as I should have, it appears that what I'm trying to do isn't possible. Hard to believe that something as simple as counting colored cells across rows that are formatted by conditional formatting in columns, can't be done. I've looked for other options to do this and can't find anything that suits me. Is there a way this can be done?
Highlighted

@Jamil Mohammad 

 

Hi!  I came across to this thread coz i was looking for an answer to my problem.  And found that you posted one for "sum by color when colors are set by conditional formatting"

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!

Highlighted

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.

Highlighted

Hi Alex,

 

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

Highlighted
Hi Pau_Me

I have already answered to this question in another forum. Please get the code or example workbook from here

https://techcommunity.microsoft.com/t5/Excel/sum-by-color-when-colors-are-set-by-conditional-formatt...
Highlighted

@bowler813 

 

Hi, 

 

you do not need UDF for this, you can accomplish the same with this formula

 

=SUMPRODUCT((--(B3=MAX($B$3:$B$12))+(C3=MAX($C$3:$C$12))+(D3=MAX($D$3:$D$12))+(E3=MAX($E$3:$E$12))+(F3=MAX($F$3:$F$12))+(G3=MAX($G$3:$G$12))+(H3=MAX($H$3:$H$12))+(I3=MAX($I$3:$I$12))))

 

also see it in the attached workbook.

Highlighted

@Jamil Mohammad 

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

 

Jeff Cupps

Highlighted

@Jamil Mohammad 

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

How?

 

2. The count conditional formatting cell per color isn't working (O5) 

 

Thanks for your support

 

Highlighted

@bowler813 

 

Hi,

 

I have revised the formula to take care of the blanks.

 

please see attached.

Highlighted

@Oscar_PT 

 

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.

 

 

Highlighted

@Jamil Mohammad 

 

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

 

Jeff Cupps

Highlighted

@Jamil Mohammad 

 

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.


Thanks

 

Highlighted

@Oscar_PT

 

You can use this formula =SUMPRODUCT(1-(COUNTIFS($C$42:$G$10010,C5:L5)>0))

 

please see attached file. 

Highlighted

@Jamil Mohammad 

 

Hi,

I've been trying to adapt your code to count cells in a range that have fonts that are conditionally formatted (bold-red or bold-green). The cells use a formula to decide the condition. I found this simple function code below but it looks for font color rather than conditional formatted font color. Any help you can provide would be awesome. 

 

Function CountColour(rng As Range, clr As Range)
Application.Volatile
Dim c As Range
For Each c In rng
If c.Font.Color = clr.Font.Color Then
CountColour = CountColour + 1
End If
Next
End Function

 

Thanks,

Highlighted
please see in my earlier posts and find the function which is for count based on the font color.
once you found it, then change the object property from font.color to font.bold
Highlighted

HI Jamil.

 

I went through all the discussion here and yet I can make my excel sheet to work with this code, I just get 0.

 

Can you have a look at the data.

 

I am attaching the macro-enable sheet and the one without it.

 

I want to have a summary table of how many people have the training completed, about to expired, experied, and never been taken.

 

RGS.

Highlighted

@AngelRodriguez 

 

Hi,  no need for UDF. I have build formula to do the calculation.

 

the word TODAY in the formulas are a named range.

 

please see attached.

Highlighted

Hi @Jamil Mohammad.

 

Thanks for your help, it is working now, I don't quite understand what was the reason for this error.

 

Another thing is that I just noticed my color coded formating is not working as I expected, I set the cell date within 45 days from today to change to Orange color which are the trainings about to expired. and if the date in the cell is less or equal to today´s date should turn into red but it is not working, it is happening to cells over 45 days I think.

 

I also noticed that if the cell changed to orange is it not counting yet in the summary, can you have a look one more time and thanks in advance.

 

By the way happy new year from Argentina.

 

RGS.

 

Highlighted
You have set the Conditional formatting incorrectly. instead of putting TODAY()+45 you have put TODAY()-45
Highlighted

@Jamil Mohammad 

 

Hi Jamil, Your code is great. Unfortunately it didnt work like I intend it to do, I have used it in my gantt chart and I want to count the colored cells in a week. See my Dummy. Thanks