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

[#1]

Good evening Mr. Jamil Mohammad,

I read your post and downloaded it. It works well.
But I would have a question of how to solve each line separately for each color.
Thank you for your reply and help in advance, a nice evening.

Example:

[#2]

Hi Norbi,

I will respond to your post due to the fact that i had the same challenge as you, and Jamil showed me the way around this.

If you check the previous post in which Jamil replied to me you will see that the CF range need to be exactly the row for which you need to make the count. you cannot use a range like A3:G16 because it won't work. The correct way to do it is to set like this: A3:G3. - for the first row.

And afterwards you need to use the format painter and to paste the CF formulas row by row. As a tip, if you want to make multiple formats with the format painter you can double click the format painter and it will remain active for all the pastes.

Jamil also made a video with this step in the post he replied to me.

i have attached the modified workbook

i hope it helps

Alin

[#3]

Hello Alin20

thank you very much for your answer managed to solve my problem. I realized that there were a lot of conditional formatting and could not count the colors correctly.

Thank you very much for your help again.

Hi,
norbi

[#4]

Hi Jamil,

The example that you helped me with worked like a charm but only for one conditional format formula applied for a range of cells.

Now i have a case in which i want to count all the CF cells in a row, but with multiple CF formulas.

I followed all the steps that you suggested (i hope i didn't missed something out).

I have attached the worksheet.

Thank you in advance

Alin

[#5]

Hi Alin_20.

You did not properly set your conditional formatting in your file, that is why it was not working.

I set the conditional formatting in the attached file correctly and it works now.

plz see attached.

[#6]

When I use the formula, it works rows in the column except for the 2nd row of data.  It does not count that row.  Has anyone hit this issue or found a way around it?  It correctly counts all of the other rows so I know it is looking at the condition formatting.  

[#7]

It should work. i suspect there must be an issue with the way CF range is set.
if you upload a dummy sample, i can take a look.

[#8]

@Jamil Mohammad 

Good morning Jamil!  This thread has been SO helpful!  I really appreciate it.  Of course, since I'm joining I'm having trouble lol.

I added the UDF and made sure that the CF I'm using is based on formulas however I can't seem to get the results to be stable.

This is a fairly simple application and I'm feeling pretty dumb right now as I'm sure whatever it is I'm doing wrong is an easy fix.

Would you mind terribly taking a look?  I tried a couple different formulas in the CF but even the straight-forward ones don't always end in the correct result.  Sometimes the count is working and sometimes it isn't.

Any help you can give me would be much appreciated.  This is making me nuts lol.

Thanks!

[#9]

@Laurafer 

thanks for your kind words. 

I saw your workbook and there are many issues with the CF formulas you set.

Double condition set for a single cell with contradicts each other. 

so example in the cell E28 there are two formulas in CF and both of them contradicts each other.

=IF(E28>D28,"true","pass")
=IF(E28<C28,"True","pass")

another issue is that you set IF formulas to return test in CF which is not correct way to set formula.

for example if you want a condition to turn red if certain condition is met then you can simply use equal sign and there is no need for IF function, in fact IF function ruins it.

for example if you want to turn cell E28 to red if its value is greater than D28 then you can simply use =E28>D28  simply as this. CF will return True if condition is met and false if it is not met.

Please see attached your workbook.

I have added two more UDFs so that you can debug and find the problem.

The first UDF return the formula used in the CF for that cell

Function CondFormulaformula(myCell, Optional cond As Long = 1) As String

CondFormulaformula = myCell.FormatConditions(cond).Formula1
End Function

this second formula evaluates the formula used in CF and returns the evaluated value.

Function CondFormula(myCell, Optional cond As Long = 1) As String

CondFormula = Application.Evaluate(myCell.FormatConditions(cond).Formula1)
End Function

you can see in the column H which i highlighted in yellow.  the evaluated CF from your formulas and they are not consistent.  with the help of two addtional UDFs you can debug your CF formulas and get  the correct result.

Hope this helps.

[#10]

@Jamil Mohammad 

That worked perfectly!  Thank you so very very much!  One more question...Is there any way to have the cells that have no data in them remain uncolored?  I was playing with the formulas but as I'm sure you've figured out by now I'm not the best at them.  Everything else is working beautifully and I am grateful for what I've got but...  :)

[#11]

You are most welcome.

Yes. you can simply add another condition that cell ="" then select "No color" in the fill

[#12]

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

[#13]

@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

[#14]

@Jamil Mohammad 

Hi there!  I tried adding the formula E$11="" and format is blank but then the COUNTConditionColorCells formula returns #Value!  Any idea what I'm doing wrong? 

[#15]

try to see if you have already a CF set for that cell with a different formula. then it will work. so you will need to use a workaround. use countblank function instead.

[#16]

Hi hiepkt12

you have posted the same question in another thread as well. I have replied to you in there.

[#17]

@Jamil Mohammad 

Hi Jamil,
Thank you. Your replies to this post have helped me so much!

I used your code and included Application.volatile. But, I can't get my spreadsheet to automatically calculate the number of coloured cells. It will only update the number of cells if I edit the cell in the formula bar. Is there a way I can set up a button on my spreadsheet that will force excel to calculate how many cells have been coloured?

[#18]

Hi Sonia1640

Please check that your workbook setting calculation mode is "Automatic"

on the Formula Tab click on "Calculation Option" and from the dropdown plz make sure "Automatic" is selected. If it is Manual then change it to Automatic.

[#19]

Hi Jamil,
The workbook is set to automatic. Do you have any other ideas?

[#20]

Hi Sonia,

Plz check if you have a personal excel file in the Excel start up folder. Also, check if you have any Excel Add-ins enabled.

If the above-mentioned did not work. then place this code below into sheet module not in a regular module.

You can change the range A2:Z100 to the actual range where your formulas are placed.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Range("A2:Z100").Calculate
End Sub

[#21]

@Jamil Mohammad Hi Jamil, I thank you for your work and effort to help.
But, it seems your code is not working here because I need to see conditional formatting changing colors at columns by the values of the cells and I need to count those color changes at rows direction, after that.
Do you think that it can be done?

[#22]

Hello,

I have try your VBA formula for sum by conditional formatting color, however formula return "0" result when data are in negative. I did conditionally formatted negative data by using ABS formula. I have staff attendance record which shows short hours by negative sign and excess hours by positive numbers. now I need to sum all short hours and excess hours first by highlighting through conditional format and then sum up those hours.

would you please help me to achieve this.  

[#23]

I did not see any conditional formatting in your file.

so, I did not understand what were you trying to achieve. If you want to sum the total of cells that are negative then you can use lets say for Column A as an example formula is =SUMIF(A2:A40,"<0") this will sum all values in cells that are negative. but if you want to count cells that have negative values then you can use =COUNTIF(A2:A40,"<0")

and to sum the positive numbers =SUMIF(A2:A40,">0") and to count the positive numbers =COUNTIF(A2:A40,">0")

[#24]

@Jamil Mohammad 

Hi Jamil,

Thank you for response. 

Sorry I did not make clear what I'm looking for. I do not want all negative to be sum only those cell value which is greater than -1.5 and less than -10.00 should be colored and sum and similarly all the cell value greater than positive 1.5 are to colored and sum.

I have done conditional formatting in attached file and try to apply your formula but its not working. Please help me out with this.

Thanks again. 

[#25]

@nashsulanki 

In cell CL2 put this formula and drag down =SUMPRODUCT((ABS(BP2:CK2)>1.5)*(ABS(BP2:CK2<10)))

and in CM2 put this formula and drag down =SUMPRODUCT((--(BP2:CK2>1.5)))

[#26]

@Jamil Mohammad 

With this formula it is counting colored cell. For example when I copied the formula to CL2 result showing 22 which is count of color cell (or condition of greater than -1.5 and less than -10). I want sum of all color cell. 

Thank you. 

[#27]

@Jamil Mohammad 

I am so VERY impressed with your expertise in MS Excel. I'm trying to do this very same task of counting cells based on CF color. I've attempted your code but get "NO-COLOR" as the output. I've attached a dummy file so you can see what's going on. 

The only thing I can figure is that it's a different version of excel or I have a setting blocked somewhere.

Thank you so much for your kind consideration and help.

[#28]

@nashsulanki 

if you want to sum instead of count then use =SUMPRODUCT((ABS(BP2:CK2)>1.5)*(ABS(BP2:CK2<10)),BP2:CK2)

[#29]

@DarylMeese 

Thanks for your kind words.

I looked at the file you attached.

If you read my earlier posts, I have mentioned to other users with similar issues as stated below

• Your CF range does not use only formula, for UDF to work, CF needs to be set up only using formula and not built-in options given for example you use the option of "Format cells that contain"
• UDF range uses the whole A to M columns, whereas your CF ranges are completely different. for example some of them starts from row one and some in row 2 ,   another CF uses double column applied range $H$2:$H$31,$J$2:$J$31 
• For UDF to work, the range in UDF and ranges in CF must match and the CFs needs to be set using only formula.

Hope this helps.

[#30]

@Jamil Mohammad 

Hi Mr Jamil

I would like to thank you for this formula. It works great! However, I have a query. Please see attached file. I have a set of KPI’s for disciplines to meet. When I use your formula to count, ‘No of disciplines met by KPI’, it works. However, when I used your formula to count, ‘No of KPI's met by Discipline’, it doesn’t work. May I know what could be wrong? Your help is greatly appreciated :)

[#31]

@Jamil Mohammad 

Hi Jamil,

Thanks for sharing the code for counting colored cells in a conditionally formatted sheet. Just a quick question, "Can you please help me alter the code to make it count the colored cells in a specific row. This current code is default for range starting from cell A3. Say suppose I want to count the colored cells for range A4 to G4 in your sheet 'COUNTBYCOLOR.xls, it does not give me correct result."

Thanks

[#32]

@emy_77 

It is because your CF is set C3:G3 and not C3:C5 

so only C3:G3 works because your CF has this range.

[#33]

@sudeep_batabyal 

if you want to count by row then your conditional formatting applied range should be also by that row and now the entire range.  so if you want it to work on A4 to G4  then set up the conditional formatting range to be applied only on A4:G4

[#34]

@Jamil Mohammad 

Hi There

I know very little about VBA's.

I've got a very large s/sheet with data that I've just inherited. I need to add this months data to the master sheet (hystoric) and I'm trying to make the process easier and less time consuming (previously done manually). It's got columns with Customer Name, Address, Territory, Serial Number, Device Name, etc (columns A to O) and then approx 6900 rows of data. In my Serial Number column I've got a conditional format (column H) (format values where this formula is true =countif(if serial number is different to last month) - fill - red) and then I've got a conditional format in column K (format values where device name is different to last month - fill - blue). I've also got subtotals - count per territory number.

What I'm trying to achieve is to count all the non-coloured Serial Numbers (cells in H) and all the change in device name (non-coloured cells in K (less non-coloured cells in H)).

I've inserted your UDF in VBA. In H373 (first change in territory) I've typed =COUNTConditionColorCells and hit Enter and get the "#Value!" error. Not sure what I'm doing wrong, please help.

[#35]

@Jamil Mohammad 

I'm trying this formula and I keep getting 0's. I'm not sure what I'm doing wrong and I'm wondering if you can help. See my data. 

[#36]

@CeliaT

Can you upload a dummy sample file?

[#37]

@ekimble83 

If you read my earlier posts, you will see that I mentioned UDF only works if CF is set using formula not build it conditions.

I changed the CF in the attached file as an example and see it works.

[#38]

@Jamil Mohammad,

Hi Jamil, trying to use the count feature on conditional formatted cells, but the count is not accurate. My range of cells are restricted to 1 row at a time of cell highlighted in blue, but it appears to give the same result, regardless of the range provided. The spreadsheet has been attached. Any thought? 

[#39]

Hi @Jamil Mohammad , 

I have a similar question, however, when I applied the codes it only counts the cell by column, which I also need it to be counted by row. I wonder if it is because each column has a different condition formatting criteria.  Could you please help? Please see the attached file. 

Thanks!!

[#40]

@Deleted 

Hi,

Your CF range was vertical while the formula range was horizontal.  so i changed your CF range to horizontal and it works now. see attached.

[#41]

@vc337 

Hi,  please read my earlier posts.  the UDF do not work if the CF is set using built-in conditions.  UDF works only when you set conditions with formula. "Use a formula to determine which cell to format"

if you set your CF using formula, then it works.

[#42]

@Jamil Mohammad 

Hi could you please check my file, i can't make it work, it gives me the error:
#VALOR!

English is not my first language, so i tried to make it work reading the posts here. Thanks if you can take a look into my file.

[#43]

@Jamil Mohammad 

Hi, I try your code and  i still cant count color in my workbook. keep saying no colour, eventough its already the same color

can you take a look at my file? really appreciate it

is it because I am using excel 2013?

[#44]

Hi

your conditional formatting range and the range used inside the UDF are not the same, that is why it does not work. please read my earlier comments to other users with similar issue.

[#45]

@Jamil Mohammad 

Hi Jamil,

I've been using your UDF for counting conditional cells and it has been working great. I was wondering if there is a way to modify the code so that when I filter my data in a table, the totals will update automatically without counting the hidden rows.

[#46]

Hi Robin,

It is maybe possible to do that, but I do not have time to write the code. I suggest you open a new question in the forum.

[#47]

@Jamil Mohammad 

I have a rather large excel file with 480 columns and 1451 rows.  I currently have conditional formatting set up to find - and color - the largest value in each row.  What I need to know is the count of row maximums in each column.  I see that your solution only works when the count function and conditional formatting are applied to the same range.  I already know how many I have per row - 1.  Is there any way to count conditional formatting when the ranges don't match?

[#48]

I could not understand your question.

If you are trying to count number of cells when condition is not matched, then you simply sort your data by color of "none" and then count.

[#49]

@Jamil Mohammad 

No, I'm trying to count when the condition is met, however, my conditional formatting runs across rows.  I need to count instances when conditional formatting is matched within columns.  As you had previously mentioned to someone else, your tool for counting conditional formatting only works when the range and the area being counted match.  My question is: is there a workaround for when the conditional formattting range and the range being summed do not match?

FYI the same condition is being tested for within each row for each row within the summed columns.

[#50]

I know it is possible to sort/filter via color and I could do that - and manually count - for each of my 480 columns; but since my data will change frequently over the course of the project, I'd prefer not to manually count 480 columns dozens, possibly hundreds, of times. I would much prefer to have a readout listed for each column.