Forum Discussion
sum by color when colors are set by conditional formatting
Hi matt nipper,
since you mentioned that you "have done an exhaustive search online" i have come up with a solution for you, although it has two limitations A) it will only work, if your rules of conditional formatting is created using conditional formatting rules with formula aka (use a formula to determine which cells to format) and B) the UDF will only work if sum range is more than one cell another word, it will not sum a single cell, as well as the conditional formatted range is more than one cell.
the example file, you can download it from here. I could not upload it here, as it is a Excel Macro-enabled Workbook that contains the UDF
Function SumConditionColorCells(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 + CFCELL.Value CF3 = CF3 + 1 Next CFCELL Else SumConditionColorCells = "NO-COLOR" Exit Function End If SumConditionColorCells = CF2 End Function
in the attached example file you can see that from A3:G16 cells are formatted using Conditional Formatting.
User Defined Function (UDF) is placed in cells J2 & J3 and cells I2 and I3 are the criteria color used as reference inside the UDF in J2 and J3.
I hope this helps you.
Edit: uploaded file and updated the code to the correct one.
Yes, it is possible and here it goes.
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
Hi, I am trying to get a count of each row in your attached example, but when I am passing the row information, it only works correctly for the first row and then the others give the same result. Any way to get this done?
- you are welcome cp411.
thanks for your feedback. - Actually figured it out! I have to have the cell in the first list match the header in the block of other cells. Thank you so much for your help!!
Thank you so much for your quick reply! I've reviewed the file but I still have one question. How can I count all the filled cells in a row and tally them in the "Total Days" column? For example, in row 3, I'd like to keep a running total under "Total Days" of all the filled cells in that row. Is there a way to do that?
Hi cp411,
Thanks for your kind words.
I looked at your file. You do not need to overkill it with UDF and too many repetitive conditional formatting rules.
You just need a simple formula like this one, I wrote for you.
First, I deleted all of the conditional formatting rules that had the pattern style in the range area C3:ND16.
Then I placed one single formula =IF(COUNTA(OFFSET(INDEX(C$18:C$114,MATCH($A3,$A$18:$A$114,0)),1,0,5,1))>0,1,0)
and copied down and right in all range up to C3:ND16.
then, I selected area C3:ND16 and right clicked and then clicked on "Format cells" and then "custom format" then used three semicolons as the custom format like this ;;;
click ok and it has hidden the content of the cells from visibility.
then I selected C3:ND16 range and created one single Conditional formatting rule =C3=1 and selected fill pattern style and clicked ok.
Saved it. and uploaded it also here.
you will see that this solution is much more simplified and robust.
please see attached workbook.
Hi Jamil-
Apologies in advance if this inquiry is in the wrong place or if you've answered this particular question before but I am having an issue that I am hoping you can help with. I have tried using your UDF and it works, but it doesn't appear to be exactly what I need. Currently, my sheet is set up in a way that if any cell in a column is filled in a certain range then another cell outside of that range will be filled with a color. I would like to count the cell that is filled, not the cell that has the info inputted. Does that make sense? I've attached my spreadsheet.
You have been so helpful with everyone on this thread and I hope you can assist me as well.
Thank you!
- MrsLSkinner
paint brush should work. can you upload a dummy sample file? Hi Jamil!Jamil
This is my conditional formatt rule for January, person 1:
(Person 1, ROW 7) =AND(C9="EL",C9<>"") ------- Applies to =$C$7:$Y$7
When I click the paint brush and paste to other rowns, the formula doesnt change, it just adds to the applies to box like the below:
(Person 2, ROW 11) =AND(C9="EL",C9<>"") --------Applies to =$C$7:$Y$7, $C$11:$Y$11
(Person 3, ROW 15) =AND(C9="EL",C9<>"") ------ Applies to =$C$7:$Y$7, $C$11:$Y$11, $C$15:$Y$15
And so on...
I need my conditional formatting to be:
(Person 2, ROW 11) =AND(C11="EL",C11<>"") --------Applies to =$C$11:$Y$11
This is my formula to average the coloured green cells with time values:
=SumConditionColorCells(C7:Y7,JD4)/COUNTConditionColorCells(C7:Y7,JD4)
I have already completed 1 full row for 12 months for 1 person, this has taken me all night!! Are you able to advise why my rule is not following sequence when copying using the paintbrush as per the video you posted?
I appreciate your help with this, I am stuck in glue with this and I dont fancy completeing conditional formatting for 740 rows!!
X
F M
If you examine in detail, your workbook conditional formatting has one rule and it is applied in the whole range of $A$3:$G$16 and when you use the UDF to count per each row then you are giving the UDF a range of single row $A3:$G3 while your conditional formatting range is $A$3:$G$16. Therefore, the mismatch between range of conditional formatting and the input range in the UDF. If you to make it work for per row, then you need to delete edit the conditional formatting and apply range should be the $A3:$G3 and then select $A3:$G3 and click the "Format Painter" and then select the each row separately to add the per row conditional formatting. Then your UDF per row will work.
Besides, you have locked the range to absolute reference in the UDF, so all of the rows refer to the row 3Plz see attached workbook also with a quick video that I have recorded and uploaded here, You can watch the video to learn how you can fix the problem in your workbook.
