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
You are most welcome.

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

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

@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

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

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

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

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

 

 

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.
Hi Jamil,
The workbook is set to automatic. Do you have any other ideas?
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

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

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.  

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")

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

 

 

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

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

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

@nashsulanki 

 

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

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

 

 

@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 :)