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 

 

Hi Jamil,

 

Kindly you code does not work in case the conditional format color in Pivot table. Please advice.

 

@wael2005_morganyahoo 

 

Hi, it does work with Conditional formatting on Pivot tables.  please see the example attached.  If it did not work for you. perhaps, you are using the wrong range or your CF is not set as it should.

 

@Jamil Mohammad 

Hi Jamil,

Your posts are very well written and informative, many thanks for sharing your knowledge.

I am however struggling to apply the same code and logic to work on my sheet.

Please refer to the attached document, within the work sheet please go to the 'Labour forecast' tab and see that within column G (Cell G15 to be precise) I am trying to get it to count the coloured cells by condition formatting in the corresponding row.

I am getting 'Name' error return despite having the work book saved as a macro enabled sheet.

any help would be great.

 

much appreicated.

 

Thanks you

@scott_terry87 

 

Thanks. 

 

I looked at the file you shared.   the reason you are getting NAME error, because you have many UDFs in the workbook, but none of them were the one I shared in this thread. which is this one 

 

 

 

Function COUNTConditionColorCells(CellsRange As Range, ColorRng As Range)
Application.Volatile
Dim Bambo As Boolean
Dim dbw As String
Dim CFCELL As Range
Dim CF1 As Single
Dim CF2 As Double
Dim CF3 As Long
Bambo = False
For CF1 = 1 To CellsRange.FormatConditions.Count
If CellsRange.FormatConditions(CF1).Font.Color = ColorRng.Font.Color Then
Bambo = True
Exit For
End If
Next CF1
CF2 = 0
CF3 = 0
If Bambo = True Then
For Each CFCELL In CellsRange
dbw = CFCELL.FormatConditions(CF1).Formula1
dbw = Application.ConvertFormula(dbw, xlA1, xlR1C1)
dbw = Application.ConvertFormula(dbw, xlR1C1, xlA1, , ActiveCell.Resize(CellsRange.Rows.Count, CellsRange.Columns.Count).Cells(CF3 + 1))
If Evaluate(dbw) = True Then CF2 = CF2 + 1
CF3 = CF3 + 1
Next CFCELL
Else
COUNTConditionColorCells = "NO-COLOR"
Exit Function
End If
COUNTConditionColorCells = CF2
End Function

 

 

 I noticed overlapped conditional formatting rules, as well as inconsistency of ranges used in UDF versus ranges used in the conditional formatting.  So, even if you place the above UDF in the workbook. it will not work, because of the aforementioned issue of inconsistent range and overlapping CF.

 

I thought maybe you are overkilling this by using UDF, whereas the solution you are looking for can be achieved by using built in functions.

 

I rebuild the CF and placed some formulas in the cells O to QG and formatted those cells as custom format ;;; which shows nothing, while there is a value in it. 

 

then applied a CF that if any of those cells hold value of 1 then show green. also those cells has nested IF formula to return 1 if the column E is not blank and is greater than row 14.  then in column G, I have used a COUNTIF formula to count if row of O to QG hold a value of 1.

 

Please see attached and let me know if it works.

 

 

 

 

l@Jamil Mohammad 

Many thanks for the response and taking the time to help me. If only more people were so kind

.

I agree there were certainly some conflicts within the code and CF rules, however I believe the fundamentals of what I am trying to achieve has been lost and subsequently the problem remains unsolved. 

 

please see attached sheet with some additional comments. 

Hopefully it all makes sense, and thank you again. 

For your ease please remember it is within Labour forecast tab, and please note that the it has a 'X&Y' axis frozen pane.


@scott_terry87 

 

Hi again,

 

I have made some changes to the formula in the attached file as per your comments in the workbook.

 

 

Hi, Does this work on 2019 excel?
It works in any version of excel

@Jamil Mohammad Thank you for all the help in this thread. It has been of great help.

 

However, I cannot seem to figure out why the code does not work for my workbook 100% of the time. It seems to skip cells. 

 

I am using CF to give a cell the color greed/red if the date is earlier/later then proposed. I then want to count the amount of red/green cells in a specific column. 

 

In the attachment I have placed an example. The formula is placed in W8 and is looking at column J.

@Jamil Mohammad 

Hi! Hopefully you are doin well.

I used below UDF at my workings , But outcome is not coming as expected.

Please need your kind help on this issue.

Function COUNTConditionColorCells(CellsRange As Range, ColorRng As Range)
Dim Bambo As Boolean
Dim dbw As String
Dim CFCELL As Range
Dim CF1 As Single
Dim CF2 As Double
Dim CF3 As Long
Bambo = False
For CF1 = 1 To CellsRange.FormatConditions.Count
If CellsRange.FormatConditions(CF1).Interior.ColorIndex = ColorRng.Interior.ColorIndex Then
Bambo = True
Exit For
End If
Next CF1
CF2 = 0
CF3 = 0
If Bambo = True Then
For Each CFCELL In CellsRange
dbw = CFCELL.FormatConditions(CF1).Formula1
dbw = Application.ConvertFormula(dbw, xlA1, xlR1C1)
dbw = Application.ConvertFormula(dbw, xlR1C1, xlA1, , ActiveCell.Resize(CellsRange.Rows.Count, CellsRange.Columns.Count).Cells(CF3 + 1))
If Evaluate(dbw) = True Then CF2 = CF2 + 1
CF3 = CF3 + 1
Next CFCELL
Else
COUNTConditionColorCells = "NO-COLOR"
Exit Function
End If
COUNTConditionColorCells = CF2
End Function

 

Hello @Jamil Mohammad !

I've tried to read your answers and I have managed to install the UDF for counting condition colored cells, but at the most I am only managing the formula to say "NO-COLOR" (sum is not relevant but I've installed it too). I have looked in your example files and the video tutorial you made, but I am still not managing to get it to work, macros should be enabled. I need the sheet to count the amount of red/green cells both horizontally and vertically. The cells are formatted so that when a number within range is added to the cell it turns green, but if a number out of range is added or if the cell is empty it goes red - that part is already done (ranges are written above the cells in blue). Now I just need the color counting in order to calculate what passed and what failed. I have attached a copy of my excel document, and I would be really grateful if you had the time to look through it and check what's missing.

Kind regards, Madeleine Larsson

(English is not my native language, so I apologize if any words are improperly used)

@Thanathozia 

 

I checked your file.  the conditional formatting you set is not using a formula that is why it is not working. 

@Afsarulbd 

It does not work because your conditional formatting "Applied range" is not the same as the range used in the UDF.

 

@MatsRJ 

 

The conditional formatting you set in that file you shared, were not accurately set. it had overlapping conditions and also the ranges were not set properly.  Also the conditional formatting rule for blank cells were not used using formula.

 

as an example, attached is the example, that shows how the UDF works. I got rid of all CFs and left a simple one to show how it works. plz see attached. 

Hi! Okay, I've only ever been taught (both in school and at work) to use conditional formatting in the way of choosing between pre-existing settings (new rule - choose a type of rule - format cells with...). But how would a formula look for, for example, the first cell having a range between 0,5-4? In range should be light green, blow/above range + empty cell + cell containing "-" should be light red.
The formula would look like this =AND(C3>=0,5,C3<=4)

@Jamil Mohammad 

=@COUNTConditionColorCells working on 1st row of table and for table total, but returns incorrect values for subsequent rows.  Can you help?

AECRoy_0-1611086345755.png

Actual values in cells(U22:U25).  Calculated values in cells (B33:B40).

Spreadsheet attached

@AECRoy 

 

the CF range was not consistent with the range used in the UDF. I modified in the CF applied range and it works now. please see attached.

@Jamil Mohammad 

I have followed the UDF but it still could not work. Can you please help?

@Jamil Mohammad 

Hello Jamil,

I've been trying to add your UDF to a file that i made to check serial numbers on boxes that were shipped, since the shipping department in the company i work in is a mess when it comes to following a sequence in serial numbers and instead they ship whatever box is closer to them, so i was wondering if you could take a look at it I feel that the formula on CF is the problem since i have a formula for each column of shipped boxes 10 in total instead of just one that would work.

 

Thank you in advance