sum by color when colors are set by conditional formatting

[#1]

Hi, Sir Jamil! Could you kindly help me how to make the UDF and formula you shared on counting the number of rows colored thru conditional formatting work? I copied and paste the UDF for this and followed the formula in your attached sample file, but I cannot get it right. The total is always zero. Here's the file I am working. Thank you very much!

[#2]

Hi,

I have a problem too and I don't know how to fix it.

I would like to count colored cells for each line but it doesn't work.

Do you maybe know what's wrong in my excel file?

[#3]

Hi Julius835,

As you can see in my earlier post. The range inside UDF and the range of CF both must be the same, otherwise the UDF does not count. Perhaps It would be easier if you use SUMPRODUCT function to count conditionally based on multiple criteria.

Put a helper column next to column E. you can hide that if you want. put real dates in them and then use something like =SUMPRODUCT(--(MONTH(F:F)=MONTH(TODAY())),--(YEAR(F:F)=YEAR(TODAY())))

[#4]

Hi Jo_Nolaj,

I looked at your workbook, the conditional formatting range and the range used in the UDF was not consistent. i changed it now and it works. plz see attached.  please note that instead of 4 CF you could set them with OR function all in one. plz see the CF which i set. the only thing is that CF is replicated for each row using format painter. 

[#5]

Hi SC_8,

I checked your file.  There CF range and the UDF used range in UDF were not the same. I modified the range by changing the CF for each row separately and therefore the UDF works now. plz see attached.

[#6]

Thank you very much for helping me, but actually I have a probelm also with the file you edited.

When I open the file is absolutely perfect, but when I just click on the cells in which appears the number of colored cells per row (without making any kind of modification), it appears #VALOR and there is no way to make it work again. Same thing if I continue my work adding rows and I try to drag the column.

Thank you very much for help!

[#7]

Thank you very much for helping me, but actually I have a probelm also with the file you edited.

When I open the file is absolutely perfect, but when I just click on the cells in which appears the number of colored cells per row (without making any kind of modification), it appears #VALUE and there is no way to make it work again. Same thing if I continue my work adding rows and I try to drag the column.

Thank you very much for help!

[#8]

HI SC_8

plz check that your calculation method is not Manual. it should be automatic. also check that macro is enabled. if both of these fails then open the module and insert Application.Volatile just after the second line.

[#9]

heyy all,

i need your helps about "How to count cells by colors with conditional formatting"

i tried ur datas but it didnt.

its so important for me.

Pls help me.

[#10]

@Imhuman

Please read my earlier post, the conditional formatting needs to be set using formula.
in your file the CF is not set using formula, that is why it does not work. if you set CF with formula then UDF will work.

[#11]

Thank you very much!

[#12]

Hi @Jamil Mohammad, firstly thanks for taking the time to help and reply to so many members. 

I have read through the whole thread and cannot find the solution to my issue.  Hope that you are able to help.

I am trying to use your count UDF to count the number of cells conditionally formatted either red/orange/green/white as per the bottom of the table.  My CF are all defined by a function, and ranges seem to match up, colours are the same as I have inputted the RGB values.  I am still getting the #VALUE error in rows 136 through 138, and a 0 count in row 139.  Maybe my CF rules are too complex?

Thanks

Aidan

[#13]

Hi  ASharp

Thanks for your kind words.

I looked at your file. the CFs are not consistent with the range used in the UDF.

for example the red color range in CF is =$AP$135,$H$4:$BK$134   double while in the UDF it is only H$4:$H$134    if you read my previous messages, you will see that most of user miss this part. 

I noticed that you you CF formulas are also having circular reference issue. 

You can simplify your workbook by using helper column/cells and perhaps a formula solution to count is much more easier than the use of this UDF.  I have given several examples in the earlier comments on this thread to other users.

Hope it helps.

[#14]

@Jamil Mohammad

Thank you for all that you have done in this thread. I've read through it all. Although some had similar issues, so far I haven't read one that's similar to mine. 

Here's what I need... For each row, there are 27 cells with conditional formatting. Based on the conditional formatting (using formulas to determine the format), the cell will either turn (standard) green, yellow or red. At the very end up each row, I have three more columns: Required, Completed, % Completed. I already have the formulas for the Required and Percentage columns figured out. Now, I simply need the formula to count all cells that have the standard green fill for the Completed column.  I tried what you have kindly offered on this thread, but it's not working for me. The two problems I'm facing are below.

Problem #1 - The UDF/formula does not count after 4 cells. 
I don't know why this happens, but the result returns as #VALUE! if I try to do all 27 cells in a row, but once you edit the range down to four cells or less, it seems to work. 

Problem #2 - The UDF/Formula seems to count all cells, instead of a specific color.
Along with problem #1, the formula seems to calculate any cell with any background color.
Let's take the following as an example: Formula =COUNTConditionColorCells(A2:C2,$E$2)
Cell E2 has a Standard Green fill. Cells in A2:C2 all have the background color of Standard Green, so the result is 3. Let's say I drag the formula down to row 3 now, which consists of all Standard Yellow. The formula *should* only count the cells with Standard Green fill ($E$2), right? In my case, it's still returning as 3.

Based off what I read in this thread, I imagine it something having to do with the conditional formatting being applied to nonconsecutive groups of cells. So, I went ahead and edited the document so I can apply each rule to one, consecutive group of cells. Unfortunately, this does not fix the problem.

There's really only one more thing that I can think of; however, I don't know if that's the main issue. There are some cells that do not contain a formula for conditional formatting. If you look at the spreadsheet, some of the cells have "NR" instead of a date. So the conditional formatting is set to "if contains." If this is the issue... what kind of formula would work to conditionally format a cell if it contains "NR"? 

I've attached a dummy file of my original one. I would appreciate your help very much! I would like the formula to be applied to AH9:AH80.

Arturo

[#15]

Hi  Arturo89

I looked at your file.

There was a lot of issues in the CF range , CF formula and the range.

I have demonstrated in the attached workbook how the CF are now calculating correctly.

things i changed in the file are the followings

Removed the CFs that was not set using formulas.

removed CFs with the whole range instead of per row

added the AND operator to only apply if cell is not blank.

applied the CF on row F3 to AF3 and then used Paint brush to duplicate it for all other rows.

your Range contained mixed input numbers and text. so i replaced the NRs with blank

please see attached workbook.

[#16]

I am trying to use your formula, and I keep getting zero count.  Here is my file.  I'm hoping that you can help me.

[#17]

it is because the formula logic used in the CF are conflicting and are not correct.

for example, your Math1 color count is 63 while your formula is =COUNTIF($F:$F, $E2) and it highlights 112 however the reason you do not see the remaining colors because they are recolored by other CR rules that takes precedent. so to make this work, you should properly set your CF formulas.

[#18]

@Jamil Mohammad 

Hi Jamil,

Thanks for all your good help here. I copied your COUNT VBA but there is problem hope you can help me with:

if the conditional formatting is done by simple formula like >50 e.g. it works.

But my conditional formatting is done with this formula: =AND(T3<=$K3,T3>=($K3-$L3+1)).

Now it doesn't work anymore. 

Can you help out?

Thanks a million!

[#19]

@soophie6 

It should work. Can you upload a sample file?

[#20]

@Jamil Mohammad 

Hi Jamil,

I tried your code but it returns "0"s. Could you be so kind and take a look at my sample file?

Thank you!

[#21]

Hi yi513

It is because the range you used in the UDF and the range set in CF are not matching.

example Range used for column N is N$6:N$205 while CF set is =$N$6:$BJ$205

plus the the condition of UDF color is in C3 where none of the colors in the range $N$6:$BJ$205 matches that color.

please read my earlier post where I have troubleshooted other users similar problems and that will give you guide on how to solve the issue on your workbook.

[#22]

@Jamil Mohammad 

I want to count the number of yellow boxes as in the file below and output in the column "count color yellow"
Thank you so much!

[#23]

@Jamil Mohammad 

I found this code to be used in my file but I want to switch from "Sub" to "Function". That is, I want the result of the yellow box displayed in the column count color yellow.

Help me!!

Thank you very much!!

Sub DisplayFormatCount()

    'Updateby20150305

    Dim Rng As Range

    Dim CountRange As Range

    Dim ColorRange As Range

    Dim xBackColor As Long

    Dim xFontColor As Long

    On Error Resume Next

    xTitleId       = "KutoolsforExcel"

    Set CountRange = Application.Selection

    Set CountRange = Application.InputBox("Count Range :", xTitleId, CountRange.Address, Type: = 8)

    Set ColorRange = Application.InputBox("Color Range(single cell):", xTitleId, Type: = 8)

    Set ColorRange = ColorRange.Range("A1")

    xReturn        = 0

    For Each Rng In CountRange

        qqq           = Rng.Value

        xxx           = Rng.DisplayFormat.Interior.Color

        If Rng.DisplayFormat.Interior.Color = ColorRange.DisplayFormat.Interior.Color Then

            xBackColor   = xBackColor + 1

        End If

        If Rng.DisplayFormat.Font.Color = ColorRange.DisplayFormat.Font.Color Then

            xFontColor = xFontColor + 1

        End If

    Next

    MsgBox "BackColor is " & xBackColor & Chr(10) & "FontColor is " & xFontColor

End Sub

[#24]

Hi Jamil!@Jamil Mohammad 

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

[#25]

@yi513

your formula to evaluate a condition is not correct. you cannot have multiple evaluation in the single condition. for example =IF(N$5<$L6, IF(N$5>=$K6, TRUE, FALSE), FALSE) is not correct. try to see how you can first put correct condition into the workbook. Also, please read my earlier posts on the limitation of UDF and also the troubleshootings of other users with similar problems.

[#26]

hiepkt12

this is not the sub routine i wrote. and it will not work for this purpose. you may need to use the function which I wrote to make it work. so, i cannot change that sub to function to make it work.

[#27]

MrsLSkinner

paint brush should work. can you upload a dummy sample file?

[#28]

@Jamil Mohammad 

@Jamil Mohammad 

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!

[#29]

@cp411 

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.

[#30]

@Jamil Mohammad 

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?

[#31]

@cp411 

you can simply use =COUNTIF($C3:$ND3,1)  in B3 and copy down.

Please see attached.

I also removed the UDFs from your file and saved it as normal xlsx file. as you no longer need to xlsm format.

[#32]

@Jamil Mohammad 

Thank you! That worked great! I have one more question and hopefully I'll be set. I've tried changing the name of Project 1, Project 2, etc., but when I do the entire corresponding row is filled with the color. How can I change the text in column A without the row filling?

[#33]

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

[#34]

you are welcome cp411.

thanks for your feedback.

[#35]

@Jamil Mohammad 

you look like the man. I'm trying to do something simular to what you have done here. 

I want the hyperlinked cells on the overview sheet to turn red if any cell on any of the hyperlinked sheet is red, and green if there isn't a red cell. there is a lot of conditional formatting going of for the cells so hard to to duplicate into the one cell.

I have tried GetColor on the the sheet but just cant get it to get a response, I have got the Bankhead hyperlink cell on Farm overview (sheet 1) to turn red by Bankhead (sheet 6) cell AP4 with conditional formatting, but just need a formula to get a response from the above criteria

I have now tried adding this VBA but that isn't working either

not sure if there is an easier way or some part of the code is wrong

[#36]

@Ade_Salmon 

Thanks for your kind words.

I looked briefly at your file. Found couple of issues.

A) your conditional formatting are not consistent. For example Worksheet BankHead column O has 4 rules in conditional formatting. then your column Q only has 1 rule and column R has 3 rules.  so, there is no clear pattern for a formula to determine the count.  If your columns CF were having the same pattern of rules, then we could have used a simple formula in a helper cell for your overview sheet to turn red if any of those columns cells condition were met. For example this formula =SUMPRODUCT(--(C3:AL3<C1)*(LEN(C3:AL3)=5))-COUNTIFS(C3:AL3,"X")  it checks if the cells are date, it then checks if the dates are smaller than the today's date, then finally if they are done which has X then it will exclude it. this formula you could have used for red color, if your columns had consistent rules. but they haven't, so this formula is returning incorrect value. This formula gives you an idea how you can make it work, by fixing your CF rules.

B) you have used two many unnecessary If functions with ISblank function to evaluate if the cells are not empty. This has caused your workbook to become sluggish. instead of =IF(ISBLANK(A5),""   you could first evaluate that If cell is not empty then do that without the use of ISBLANK function. The way it is now, it first evaulates every cell if it blank or not.  so, you could use something like =IF(A5<>"",$A5-7*4,""). 

C) TODAY functun is volatile, using it multiple time in everysheet is slowing down your workbook.

instead you can have one single cell with TODAY function and all other formulas are referenced to this single cell. in fact you could have one single named range with =TODAY() function and it is used in your formulas across the sheets.

D) you have pre-populated 500 rows in each of the sheets with the unnecessary formulas. I understand that you need to have those formulas in case if the data grows, however you can avoid those 500 cells with pre populated formula by using Excel Tables feature, which has dynamic nature that when you add new data all formulas from the above cells are automatically copied down. so your workbook will work much faster and more dynamic with Excel Tables rather than these 3500 rows with formulas which sucks the CPU.

Hope it helps.

[#37]

Jamil

that is brilliant I am only an amateur at this piecing together the bit of knowledge I know to make this for my wife, not used tables before but I will look into correcting the spreadsheet with you recommendations

thanks a lot

Ade

[#38]

@Jamil Mohammad 

Hi Jamil,

done everything you suggested, it has worked a treat but still cant get the overview cells to change to red or green from their relevant sheet

hope for some assistance 

[#39]

@Ade_Salmon 

Great!  now only little has remained.

Please see attached workbook. I have placed a formula next to the Bankhead icon in cell A5 of Overview sheet.   this is the formula =SUMPRODUCT(--(Bankhead!C3:AL3<Bankhead!C1)*(LEN(Bankhead!C3:AL3)=5))-COUNTIFS(Bankhead!C3:AL3,"X")  

then I used custom formatting and in custom formatting I put ;;; three semicolons, so that the value returned is not visible. Then I created a conditional formatting on cell B5 that If cell A5 is greater than zero then turn red.  

You can replicate this with the other worksheet icons as well.

Hope it helps.

[#40]

Jamil

works a treat, you are a wizard

thanks a lot

[#41]

@Jamil Mohammad  

I've study this thread for few days already... I have a problem that I want to solve, in my example I was playing with a personal project, and basically what I want to do is after using conditional formatting, I'm using a match function, I'm highlighting a few numbers, what I want to finally obtain is the COUNT of each matching numbers on each row... what I have done is a double filtration based on a combo box which then print diferent lists and then match numbers based on CF.

I like to say about myself that I'm proficient in using Excel, but I'm kinda stuck on this problem!

I did try diferent approaches and examples that I found on this thread, but still didn't find the correct combination.... 

[#42]

@Cristian1980 

You can use SUMPRODUCT with COUNTIFS.  COUNTIFS criteria can do function argument array operation when we place a range instead of single criteria and then we wrap it with SUMPRODUCT to eliminate the need of special keystroke.

I placed =SUMPRODUCT(COUNTIFS(D3:O3,$D$1:$I$1)) in P3 and copied down.

please see attached file with formula.

[#43]

@Jamil Mohammad 

Thx! for the idea, I was kinda fixed on the counting using scripting, because the usual COUNTIFS from excel didn't give to much initially, now I understand that it did requiter an other combination; now I can continue my project. Thx!

[#44]

@Cristian1980 

You are welcome.  Thanks for feedback.

[#45]

@Jamil Mohammad 

Thank you for this, i have been trying to trouble shoot my issues for the past week or so to no avail. The problem is I need to use quite a few different colors for conditional formatting. I have made sure that all the colors match in terms of RGB between the painted cells and the conditional cells. But after the first 9 (the 9/10 standard pallet colors) the math doesn't seem to be accurate any longer. Is there anyway you can take a look at my file and see if there is anything I can do to fix this or if you have any advice on this?

[#46]

@Mitchell_Wade 

Hi,  perhaps you may have not read my earlier posts to similar issues.

Your conditional formatting range in the CF and inside UDF were not matching.

I change your CF applied ranges in a way to match the UDF and now it works, see attached.

[#47]

@Jamil Mohammad 

Thanks for taking a look at it, but I am still seeing the errors even in the preview version you sent. The times in Column D do not sum to their corresponding colors in column H and Column M. This is the issue I am speak of. for example. if you look at D4 and H4 they are not the same at all. D4 is calculated to be 20 mins while H4 is only calculating 7 minutes.

[#48]

@Mitchell_Wade 

it worked and i tested it.  try to remove the time format and change it to just normal general number format.  then add another acitivty in column A for example 15) and then add a value in column D and then it will see that UDF adds up that amount into corresponding color of the  15)

[#49]

Hi @Jamil Mohammad, the sample file uploaded is still showing #Value error message.

I am getting a ZERO result when applied.

I have copied the CountConditionColorCells UDF and have conditional formatting on a row, which I am trying to count.

I am also using this in cell A6:  =CountConditionColorCells(C6:V6,A6)

Row C is the conditional formatting row. The CF is working correctly, but the UDF is not co-operating.

Any ideas?

This is the EDF I have copied:

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 + CFCELL.Value
CF3 = CF3 + 1
Next CFCELL
Else
SumConditionColorCells = "NO-COLOR"
Exit Function
End If
SumConditionColorCells = CF2
End Function

I would appreciate any help you can provide.

Thanks,

Van

[#50]

@Jamil Mohammad 

Dear Sir, i have been trying to count the number of cells having two separate conditional formatted colours in a leave planer calendar. However after applying the VBA code that have been given by you i am getting the result as only 1 in my target cell. i have atfached the file, hoping for your help