How to sum and count cells by color in Excel

In this article, you will learn new effective approaches to summing and counting cells in Excel by color. These solutions work for cells colored manually and with conditional formatting in all versions of Excel 2010 through Excel 365.

Even though Microsoft Excel has a variety of functions for different purposes, none can calculate cells based on their color. Aside from third-party tools, there is only one efficient solution - create your own functions. If you know very little about user-defined functions or have never heard of this term before, don't panic. The functions are already written and tested by us. All you need to do is to insert them in your workbook :)

How to count cells by color in Excel

Below, you can see the codes of two custom functions (technically, these are called user-defined functions or UDF). The first one is purposed for counting cells with a specific fill color and the other - font color. Both are written by Alex, one of our best Excel gurus.

Custom functions to count by color in Excel
Function CountCellsByColor(data_range As Range, cell_color As Range) As Long Dim indRefColor As Long Dim cellCurrent As Range Dim cntRes As Long Application.Volatile cntRes = 0 indRefColor = cell_color.Cells(1, 1).Interior.Color For Each cellCurrent In data_range If indRefColor = cellCurrent.Interior.Color Then cntRes = cntRes + 1 End If Next cellCurrent CountCellsByColor = cntRes End Function Function CountCellsByFontColor(data_range As Range, font_color As Range) As Long Dim indRefColor As Long Dim cellCurrent As Range Dim cntRes As Long Application.Volatile cntRes = 0 indRefColor = font_color.Cells(1, 1).Font.Color For Each cellCurrent In data_range If indRefColor = cellCurrent.Font.Color Then cntRes = cntRes + 1 End If Next cellCurrent CountCellsByFontColor = cntRes End Function

Once the functions are added to your workbook, they will do all work behind the scenes, and you can use them in the usual way, just like any other native Excel function. From the end-user perspective, the functions have the following look.

Count cells by fill color

To count cells with a particular background color, this is the function to use:

CountCellsByColor(data_range, cell_color)

Where:

  • Data_range is a range in which to count cells.
  • Cell_color is a reference to the cell with the target fill color.

To count cells of a specific color in a given range, carry out these steps:

  1. Insert the code of the CountCellsByColor function in your workbook.
  2. In a cell where you want the result to appear, start typing the formula: =CountCellsByColor(
  3. For the first argument, enter the range in which you want to count colored cells.
  4. For the second argument, supply the cell with the target color.
  5. Press the Enterkey. Done!

For example, to find out how many cells in range B3:F24 have the same color as H3, the formula is:

=CountCellsByColor(B3:F24, H3)

In our sample dataset, the cells with values less than 150 are colored in yellow, and the cells with values higher than 350 in green. The function gets both counts with ease:
Counting cells by color in Excel

Count cells by font color

In case your cell values have different font colors, you can count them using this function:

CountCellsByFontColor(data_range, font_color)

Where:

  • Data_range is a range in which to count cells.
  • Font_color is a reference to the cell with the sample font color.

For example, to get the number of cells in B3:F24 whose values have the same font color as H3, the formula is:

=CountCellsByFontColor(B3:F24, H3)
Custom function to count cells by font color in Excel

Tip. If you'd like to name the functions differently, feel free to change the names directly in the code.

How to sum by color in Excel

To sum colored values, add the following two functions to your workbook. As with the previous example, the first one handles fill color and the other - font color.

Custom functions to sum by color in Excel
Function SumCellsByColor(data_range As Range, cell_color As Range) Dim indRefColor As Long Dim cellCurrent As Range Dim sumRes Application.Volatile sumRes = 0 indRefColor = cell_color.Cells(1, 1).Interior.Color For Each cellCurrent In data_range If indRefColor = cellCurrent.Interior.Color Then sumRes = WorksheetFunction.Sum(cellCurrent, sumRes) End If Next cellCurrent SumCellsByColor = sumRes End Function Function SumCellsByFontColor(data_range As Range, font_color As Range) Dim indRefColor As Long Dim cellCurrent As Range Dim sumRes Application.Volatile sumRes = 0 indRefColor = font_color.Cells(1, 1).Font.Color For Each cellCurrent In data_range If indRefColor = cellCurrent.Font.Color Then sumRes = WorksheetFunction.Sum(cellCurrent, sumRes) End If Next cellCurrent SumCellsByFontColor = sumRes End Function

Sum values by cell color

To sum by fill color in Excel, this is function to use:

SumCellsByColor(data_range, cell_color)

Where:

  • Data_range is a range in which to sum values.
  • Cell_color is a reference to the cell with the fill color of interest.

For example, to add up the values of all cells in B3:F24 that are shaded with the same color as H3, the formula is:

=SumCellsByColor(B3:F24, H3)
Sum values by fill color in Excel.

Sum values by font color

To sum numeric values with a specific font color, use this function:

SumCellsByFontColor(data_range, font_color)

Where:

  • Data_range is a range in which to sum cells.
  • Font_color is a reference to the cell with the target font color.

For instance, to add up all the values in cells B3:F24 with the same font color as the value in H3, the formula is:

=SumCellsByFontColor(B3:F24, H3)
Sum values by font color in Excel.

Count and sum by color across entire workbook

To count and sum cells of a certain color in all sheets of a given workbook, we created two separate functions, which are named WbkCountByColor and WbkSumByColor, respectively. Here comes the code:

Custom functions to count and sum by color across workbook
Function WbkCountByColor(cell_color As Range) Dim vWbkRes Dim wshCurrent As Worksheet Application.ScreenUpdating = False Application.Calculation = xlCalculationManual vWbkRes = 0 For Each wshCurrent In Worksheets wshCurrent.Activate vWbkRes = vWbkRes + CountCellsByColor(wshCurrent.UsedRange, cell_color) Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic WbkCountByColor = vWbkRes End Function Function WbkSumByColor(cell_color As Range) Dim vWbkRes Dim wshCurrent As Worksheet Application.ScreenUpdating = False Application.Calculation = xlCalculationManual vWbkRes = 0 For Each wshCurrent In Worksheets wshCurrent.Activate vWbkRes = vWbkRes + SumCellsByColor(wshCurrent.UsedRange, cell_color) Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic WbkSumByColor = vWbkRes End Function

Note. To make the functions' code more compact, we refer to the two previously discussed functions that count and sum within a specified range. So, for the "workbook functions" to work, be sure to add the code of the CountCellsByColor and SumCellsByColor functions to your Excel too.

How to count colored cells in entire workbook

To find out how many cells of a particular color there are in all sheets of a given workbook, use this function:

WbkCountByColor(cell_color)

The function takes just one argument - a reference to any cell filled with the color of interest. So, a real-life formula may look something like this:

=WbkCountByColor(A1)

Where A1 is the cell with the sample fill color.

How to sum colored cells in whole workbook

To get a total of values in all cells of the current workbook highlighted with a particular color, use this function:

WbkSumByColor(cell_color)

Assuming the target color is in cell B1, the formula takes this form:

=WbkSumByColor(B1)

Count and sum conditionally formatted cells

The custom functions for adding up and counting color-coded cells are really nice, aren't they? The problem is that they do not work for cells colored with conditional formatting, alas :(

To handle conditional formatting, we have written a different code (kudos to Alex again!). It works well with both preset formats and custom formula-based rules. Contrasting with the previous examples, this code is a macro, not a function. The macro counts and sums conditionally formatted cells by fill color. Please insert it in your VBA Editor, and then follow the below instructions.

VBA macro to count and sum conditionally formatted cells.
Sub SumCountByConditionalFormat() Dim indRefColor As Long Dim cellsColorSample As Range Dim cntRes As Long Dim sumRes Dim cntCells As Long Dim indCurCell As Long On Error Resume Next cntRes = 0 sumRes = 0 cntCells = Selection.CountLarge Set cellsColorSample = Application.InputBox( _ "Select sample color:", "Select a cell with sample color", _ Application.Selection.Address, Type:=8) If Not (cellsColorSample Is Nothing) Then indRefColor = cellsColorSample.Cells(1, 1).DisplayFormat.Interior.Color For indCurCell = 1 To (cntCells) If indRefColor = Selection(indCurCell).DisplayFormat.Interior.Color Then cntRes = cntRes + 1 sumRes = WorksheetFunction.Sum(Selection(indCurCell), sumRes) End If Next MsgBox "Count=" & cntRes & vbCrLf & "Sum= " & sumRes & vbCrLf & vbCrLf & _ "Color=" & Left("000000", 6 - Len(Hex(indRefColor))) & _ Hex(indRefColor) & vbCrLf, , "Count & Sum by Conditional Format color" End If End Sub

How to count and sum conditionally formatted cells using VBA macro

With the macro's code inserted in your Excel, this is what you need to do:

  1. Select one or more ranges where you want to count and sum colored cells. Make sure the selected range(s) contains numerical data.
  2. Press Alt + F8, select the SumCountByConditionalFormat macro in the list, and click Run.
  3. A small dialog box will pop asking you to select a cell with the sample color. Do this and click OK.

VBA macro to count and sum conditionally formatted cells

For this example, we used the inbuilt Highlight Cell Rules and got the following results:

  • Count (12) the number of cells in range B2:E22 with the same color as G3.
  • Sum (1512) is the sum of values in cells formatted with Light Red Fill.
  • Color is a hexadecimal color code of the sample cell.

A count and sum of conditional formats

Tip. The sample workbook with the SumCountByConditionalFormat macro is available for download at the end of this post.

How to get cell color in Excel

If you need (or are curious) to know the color of a specific cell (fill or font color), add the following user-defined functions to your Excel. It returns ColorIndex as a decimal number.

Custom functions to get the cell color
Function GetCellColor(cell_ref As Range) Dim indRow, indColumn As Long Dim arResults() Application.Volatile If cell_ref Is Nothing Then Set cell_ref = Application.ThisCell End If If cell_ref.Count > 1 Then ReDim arResults(1 To cell_ref.Rows.Count, 1 To cell_ref.Columns.Count) For indRow = 1 To cell_ref.Rows.Count For indColumn = 1 To cell_ref.Columns.Count arResults(indRow, indColumn) = cell_ref(indRow, indColumn).Interior.Color Next Next GetCellColor = arResults Else GetCellColor = cell_ref.Interior.Color End If End Function Function GetFontColor(cell_ref As Range) Dim indRow, indColumn As Long Dim arResults() Application.Volatile If cell_ref Is Nothing Then Set cell_ref = Application.ThisCell End If If cell_ref.Count > 1 Then ReDim arResults(1 To cell_ref.Rows.Count, 1 To cell_ref.Columns.Count) For indRow = 1 To cell_ref.Rows.Count For indColumn = 1 To cell_ref.Columns.Count arResults(indRow, indColumn) = cell_ref(indRow, indColumn).Font.Color Next Next GetFontColor = arResults Else GetFontColor = cell_ref.Font.Color End If End Function

Note. The functions only work for colors applied manually, and not with conditional formatting.

Get fill color of a cell

To return a decimal code of the color a given cell is highlighted with, make use of this function:

GetCellColor(cell_ref)

For example, to get the color of cell A2, the formula is:

=GetCellColor(A2)

Get font color of a cell

To get a font color of a cell, use an analogous function:

GetFontColor(cell_ref)

For instance, to find the font color of cell E2, the formula is:

=GetFontColor(E2)

Get hexadecimal color code of a cell

To convert a decimal color index returned by our custom functions into a hexadecimal color code, make use of Excel's native DEC2HEX function.

For example:

="#"&DEC2HEX(GetCellColor(A2))

="#"&DEC2HEX(GetFontColor(E2))
Custom functions to get a cell's color in Excel

How to insert VBA code in your workbook

To add the function's or macro's code to your Excel, move on with these 4 steps:

  1. In your workbook, press Alt + F11 to open Visual Basic Editor.
  2. In the left pane, right-click on the workbook name, and then choose Insert > Module from the context menu.
  3. In the Code window, insert the code of the desired function(s):
  4. Save your file as Macro-Enabled Workbook (.xlsm).

If you are not very comfortable with VBA, you can find the detailed step-by-step instructions and a handful of useful tips in this tutorial: How to insert and run VBA code in Excel.

How to get custom functions to update

When summing and counting color-coded cells in Excel, please keep in mind that your formulas won't recalculate automatically after coloring a few more cells or changing existing colors. Please don't be angry with us, this is not a bug in our code :)

The point is that changing cell color in Excel does not trigger worksheet recalculation. To get the formulas to update, press either F9 to recalculate all open workbooks or Shift + F9 to recalculate only the active sheet. Or just place the cursor into any cell and press F2, and then hit Enter. For more information, please see How to force recalculation in Excel.

Fastest way to calculate colored cells in Excel

If you do not want to waste time tinkering with VBA codes, I'm happy to introduce you to our very simple but powerful Count & Sum by Color tool. Together with 70+ other time-saving add-ins, it is included with Ultimate Suite for Excel.

Once installed, you will find it on the Ablebits Tools tab of your Excel ribbon:
Ablebits Count & Sum by Color tool for Excel

And here is a short summary of what the Count & Sum by Color add-in can do:

  • Count and sum cells by color in all versions of Excel 2016 - Excel 365.
  • Find average, maximum and minimum values in the colored cells.
  • Handle cells colored manually and with conditional formatting.
  • Paste the results anywhere in a worksheet as values or formulas.

Sum and count cells by one color

Selecting the Sum & Count by One Color option will open the following pane in the left part of your worksheet. You specify the source range and sample cell, then then click Calculate.

The result will appear on the pane straight away! No macros, no formulas, no pain :)

Apart from count and sum, the add-in also shows Average, Max and Min for colored numbers. To insert a particular value in the sheet, click the Paste button next to it. Or click Paste All to have all the results inserted at once:
Calculate cells in Excel by selected color.

Count and sum all colored cells at once

To handle all colored cells at a time, choose the Sum & Count by All Color option. Basically, it works in the same way, except that instead of color, you choose the function to calculate.
Calculate all colored cells at once.

Tip. To have the results inserted in the worksheet as formulas (custom functions), check the corresponding box at the bottom of the pane.

Well, calculating colored cells in Excel is pretty easy, isn't it? Of course, if you have that little gem that makes the magic happen :) Curious to see how our add-in will cope with your colored cells? The download link is right below.

Available downloads

Sum and count by color in Excel - examples (.xlsm file)
Ultimate Suite 14-day fully-functional version (.exe file)

Table of contents

788 comments

  1. Hi
    When I use the macro, it works fine. However, when I enter another value with font color. It doesn't refresh the sum values. Please help.

    • Hi Gregg,

      This is the standard Excel behavior. It doesn't see the change in colour as change in data. Recalculation will work if data will be changed in at least one cell in the calculated range or if you run Formulas -> Calculate Now.

  2. Need function for gettting color code of conditional fomatted cell instead of macro running and providing the count and color code.want to use that color code in if statement along with gettting color code of conditional fomatted cellfunction to perform it with next function like vlookup or some other...

    Please I think you can do it because in macro you are able to generate the color code just please provide some function to get conditional formatted cell color....

  3. Amazing! I've tried and it works great . thank u ^^

  4. How do I count SEVERAL colors as a total sum e.g. red AND blue together without first having to add these ranges to independent cells and then count the total sum of these cells using the SUM function?

    Specifically, I have several columns where the colors specify the ranges of time periods, so say, between 8 AM to 6 PM I have a light green, but past 6 PM to 11 PM I have a dark green. I want to add these two color values together in the cell at the top of the column in order to give me the total sum of colored cells in that column. I have tried to nest the code suggested on this site but it keeps telling me that it doesn't work so how do I do it? I need to nest up to 8 different colors in one function, ideally, so I can copy it across all relevant columns without having to change the content of the function, would I change the colors of the columns in the future.

    • Hi Samuel,

      You can combine several SumCellsByColor functions in one formula.
      For example, the following formula sums the cells in range A1:A10 with colors from A1 and A2.
      =SumCellsByColor(A1:A10, A1) + SumCellsByColor(A1:A10, A2)

  5. Thank you for the information you provided which is very helpful. Can you tell me how to count cells, by color only, that may have a number in each cell? Once I insert the countcellsbycolor formula into a cell along with the color cell, and in this case, it's a calendar month where certain dates are color coded, I keep getting this error message #VALUE! which is also totaling up the sum of the "date numbers" within the color coded cells. I just want to count the cells that are color coded in a particular month and not total up the numbers within those cells. Thank you.

  6. =CountCellsByColor(F2:F14,A17)

    Using the code above I get "Compile error: Invalid outside procedure" and i even copy the code & steps above above exactly...any ideas? Thank you!

  7. hi there...when i save the file as a micro enabled xls file, the formula returns a #name? error....

    Thoughts? Please help

    Thanks

  8. I have a dynamically updated calendar I created without VBA where the formula finds the last column that there is data present up to the current month and displays said data. However, the data is color coded and i was wondering if there is a way to only display cells with a certain fill color or just to exclude cells with a certain fill color

  9. Hello and thank you for this great formula!

    I have 1 thing I cant seem to figure out.

    I am using the formula from answer in question 184, but I can't seem to use it to count cells with any text (*)

    I have tried this;
    =SUM(('1sthalf'!X6:X23="*")*(GetCellColor('1sthalf'!X6:X23)=GetCellColor(A1)))

    The above returns 0 as the value, there is 1 cell with text, so answer should be 1.

    Meaning I want it to count all white fields (A1 is white) where there is text in cells X6:X23. It works once I just write "tr" which is the only cell with text, but sometimes it is many different combinations of text in the cells.

    I hope I dont have to copy this formula for every different text string I have in my range? I have approximately 100 different text strings, so I doubt excel has room for such a big formula.

    Thanks!

    • Hello Nicolai,

      Please try to use the following formula instead:
      =SUM(('1sthalf'!X6:X23<>"")*(GetCellColor('1sthalf'!X6:X23)=GetCellColor(A1)))
      The asterisk character works only for the search.

  10. Hi Rohit,

    Covert the data range to a Table.[Ctrl+T]function.
    Highlight the column[s] you want to sum/average/mean etc and set the Conditional formatting.
    In the column header of the table you can use the filter to total by selecting the colour condition you have set.

  11. When i am using the GetCellColor function by passing the cell number, no matter whatever the background color of the cell is, it is always returning the value 16777215.
    Could you kindly help?

    • I was able to figure out the problem. I have conditional formatting rules set up on the cells, which gives an impression that the cell has some background color, however in reality there is no color in the background. This function is picking the default cell color in the background instead of the one from conditional formatting. If i use this function on any other cell without having conditional formatting, it returns the correct value.
      Is there way to use this function to pick up conditional formatting color?

  12. Hi,

    I can't seem to get the countcellsbycolor function to work, do to the comma(,) in the formula. did i miss something?

    • Hi Alex,

      Most likely you have a custom locale in Excel that uses a different delimiter for separating values in the formula. Please go to Control Panel -> Region and Language-> Additional settings and replace the delimiter to get the formula to work. If you still encounter any issues, please send your spreadsheet to support@ablebits.com, we’ll take a look at it.

  13. when i use this code its gives me a error
    Compile error:

    Syntax error
    (Module1 48:0)

  14. Great job Svetlana on this "How to count and sum cells by color in Excel 2010 and 2013"

  15. Hello mam,
    i want to count quantity of particular items,its created by different codes like ABC,ADB,ACD....etc. Now i want to count a particular item means ABC=qty ?.
    I used =countif(D4:D52,"ABC") but it counts only no of codes in sheet,i want to count qty of ABC its mentioned in other coloumn and ABC code is repeating below raws. i want to calculate the qty of ABC,ADB,ACD...etc
    How it possible ???

    • Change Room CFL2 CFL 2Pin 11 Watts X 2 nos 1
      Liquid manufacturing PL3 PL Type3 36Watts X 3 nos 6
      Liquid Filling PL3 PL Type3 36Watts X 3 nos 2
      Liquid Material Entry CFL2 CFL 2Pin 11 Watts X 2 nos 1
      Liquid Washing PL2 PL Type2 36Watts X 2 nos 1
      Liquid Bottle Wash TR2 Tube Rod 2ft 14 watts X 2 nos. 1
      Liquid Packing TR2 Tube Rod 2ft 14 watts X 2 nos. 8
      Sugar Storage PL2 PL Type2 36Watts X 2 nos 5
      Carton Coding PL2 PL Type2 36Watts X 2 nos 1
      Production Entrance CFL2 CFL 2Pin 11 Watts X 2 nos 1

    • Hello Robin,

      You can use the SUMIF function like:
      =SUMIF(D4:D52,"ABC", B4:B52)

      Where column D contains your ABC, ADB, ACD, etc, code, and column B is the quantity to sum.

  16. Hey Martin, would be great if you could help me for above query....

  17. your functions are quite good to use, Is there anything where I can count basis on a given condition in cell i.e 2015 (with green color), note- there are other cell as well with 2015 red color, but I need the count for 2015 with green cell. please help
    Regards
    Mahesh

    • Sounds about like what I need. Have you gotten any response yet?

    • Hi Mahesh,

      Sorry for the delay,

      Please copy the VBA code to your workbook and then enter the following array formula (remember to press Ctrl + Shift + Enter to complete it):
      =SUM((A1:B13="2015")*(GetCellColor(A1:B13)=GetCellColor(D1)))

      Where A1:B13 is the range where you want to count the occurrence of the text "2015", and
      D1 is the helper cell with the color sample, e.g. green.

      You can also type =GetCellColor(D1) in any cell, copy the result and paste it to your formula instead of calling GetCellColor(D1). In this case you don't need the helper cell.

      • HI Alex,
        I am trying to count the number of time a cell is red and has the number 33 in it. When I try to create the array you posted on Feb 11, 2016 the value I receive in the cell is 0, when the answer should be 2. I am using =SUM((C2:C132=G13)*(getcellcolor(C1:C132)=getcellcolor(G4)))
        Thanks for the help!

        • Hello Phyllis!
          In your formula =SUM((C2:C132=G13)*(getcellcolor(C1:C132)=getcellcolor(G4)))
          instead of C1 use C2
          =SUM((C2:C132=G13) * (getcellcolor(C2:C132)=getcellcolor(G4)))
          I hope this will help, otherwise please do not hesitate to contact me anytime.

  18. Hi, I have tried to use your formula with a SUMIF formula. I have two columns; column A which is a text entry cell (project name) and column B which is a currency cell. I want a formula where I can add the sum of the cells in column B but only if the text colour in Column A is green. I tried using the formula:

    =SUMIF(A5:A12,"SumCellsByColor(A5:A12,A19)", B5:B12)

    where A19 says "Total Green Projects" in a green font. Whilst you might wonder why I don't just change the text colour in the B column and use your regular formula, in reality the spread sheet is far more complex and column B is actually many columns away from column A (and usually in a variety of different colours). If you could help in any way that would be really appreciated!

    • Hello, Martin,

      Thank you for contacting and for sending us your sample workbook. We have just replied to you by email, please check your inbox.

      • Thank you for the spread sheet however it doesn't work when I change the colour of the text of the cells in Column A; the change in value is not reflected in the formula.

        I also see that if I insert a new row, the formula shows "VALUE!".

        I want to be able to change the colour of any cells in column A (to green, or any other colour) and for the corresponding increase/decrease in the total sum of column B, which is displayed in the formula cell. As new projects arrive, I want to be able to insert new rows and have this included in the formula.
        As this is a very fluid spread sheet which is always changing, the formula doesn't seem to work with any changes. Is this something that can be fixed? Is this possible?

        • Hi Martin,

          We apologize for the delay in our reply, we get so many comments we’re having a hard time answering them all. Practically anything is possible :) Could you please send the spreadsheet with the formula you inserted to support@ablebits.com? It is very likely that you need to fix the beginning of the range with the help of absolute references, while keeping the end of the range relative.

          There is one peculiarity to consider: the Count/Sum formula is not re-calculated automatically if you set the cell color using a ribbon button in Excel, i.e. the font and fill color icons on the Home tab. However, there is a way to go around this issue, please see the following comment:
          https://www.ablebits.com/office-addins-blog/count-sum-by-color-excel/#comment-52201

  19. This was super helpful...worked with no problem..great Macro! Glad I found this site..You saved me a ton of time working in Excel 2011 on a Mac..I would have been so lost trying to figure out how to count the cells in my worksheet by color! Thank you!

  20. Hi,

    I am trying to count by colour, but within the context of an "IF" formula.
    I have a spreadsheet where I have classes which, when taught(as opposed to canceled) are filled in with the date the class took place. The row is filled in manually (entire row from left) by colour, coded to the teacher name. When a class is covered by another teacher the background colour is changed to the appropriate colour.
    From this, I have another sheet which calculates how often within a specified time range (one month for billing purposes) the class has taken place. This works fine, however, I would like to find out how many classes a given teacher has taught in a specified time range. I can simply go through the sheet and count, but I would like something to count the cells by colour within an "if" function to qualify which dates to look at, as opposed to manually choosing a section of the spreadsheet.
    If you are able to help with this, I would greatly appreciate it. If you would like to look at the sheet for a better understanding of what I am doing, I would be happy to send it to you.
    Thank you very much!
    Sincerely,
    K. MacPherson

  21. The code is not saved after exit
    tanks.

  22. Hi, have a problem with these programms, it just dont work on conditional formating...how can fix it to work fine?

    • Hello, Hugo,

      Please specify the programs you are experiencing difficulties with. What steps do you follow to accomplish the task? What result do you expect to get? It would be really helpful if you could send us a small sample table with your data in Excel to support@ablebits.

  23. Hi,

    This code is not working along with Conditional Formatting.

    Pls suggest the alternate solution.

    BR//Deepak

  24. the below code gives error in the first row

    Function WbkCountCellsByColor(cellRefColor As Range)
    Dim vWbkRes
    Dim wshCurrent As Worksheet

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    vWbkRes = 0
    For Each wshCurrent In Worksheets
    wshCurrent.Activate
    vWbkRes = vWbkRes + CountCellsByColor(wshCurrent.UsedRange, cellRefColor)
    Next
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    WbkCountCellsByColor = vWbkRes
    End Function

    Function WbkSumCellsByColor(cellRefColor As Range)
    Dim vWbkRes
    Dim wshCurrent As Worksheet

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    vWbkRes = 0
    For Each wshCurrent In Worksheets
    wshCurrent.Activate
    vWbkRes = vWbkRes + SumCellsByColor(wshCurrent.UsedRange, cellRefColor)
    Next
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    WbkSumCellsByColor = vWbkRes
    End Function

    • Hello, Manssoor,

      Looks like you haven't added the functions CountCellsByColor and SumCellsByColor before adding the code above. Please add these functions from the code in step 3.

  25. Thanks, this worked great.

  26. This is really great work. For me it worked like a charm.

  27. Hi there,

    Thank you so much for the code that allows me to sum cells by colour.

    I was wondering - is there a way for me to alter the code to allow me to sum cells that are any colour OTHER THAN a specified colour?

    That is, if I have say, a worksheet that has cells of 6 different colours - red, blue, yellow, purple, green and orange - can I tell excel to sum all cells that are NOT YELLOW? I would like cells that aren't coloured to be included (i.e. for excel to sum all cells that are red, blue, purple, green, orange and without fill on the basis that they are all NOT YELLOW).

    Thank you for your time.

    • For the record, simply putting "" ahead of the colour code in the formula does not appear to work.

      • Sorry! "" should have read as

        • Hello, Katherine,

          Please in the code where you find If indRefColor = cellCurrent.Interior.Color Then, replace "=" with "<>". Like If indRefColor <> cellCurrent.Interior.Color Then, If indRefColor <> cellCurrent.Font.Color Then

          Hope this helps.

  28. hi, it is very helpful VB but it has a little problem, when it is working if we change the cell color (which had had the color) it does not work until we click the sum cell hand press enter .

    • Hello, Reza,

      This is the standard Excel behaviour. It doesn't see the change in colour as change in data. Recalculation will work if data will be changed in at least one cell in the calculated range or if you run Formulas -> Calculate Now.

  29. I Have a doubt, if we can make an attendence with extra allowance sheet of workers and if absent days can charged a fine of $1 and multiplies the fine if absents occur in the same month as $2,$4 etc.

  30. this was so helpful :D thank you!

  31. Hi, I have a doubt!. If I will have a range for example "$A$1:$A$10", and:
    A1(color black), A2(color blue), A3, (color yellow), A4 (color white), A5 (color white), A6 (color yellow), A7 (color white), A8 (color white), A9 (color blue), A10 (color yellow).
    How I can add some cells of the same color with a macro, for example:
    A1 = SUM(A2,A9) , A3 = SUM(A4,A5) , A6 = SUM(A7,A8) , A9 = SUM(A10).

  32. The formula doesn't seem to work well with Google Spreadsheet. There are times that I simply have to refresh and voila, it will work just the way I need it to but now, I've tried several times and it just won't work.

    I used this formula.
    =COUNTA(valuesbyCellColor("color", range))

    • Hi Emmy,

      This function is written for the desktop versions of Microsoft Excel, the code won't work with Google Spreadsheets.

  33. The formula doesn't seem to work well with Good Spreadsheet. There are times that I simply have to refresh and voila, it will work just the way I need it to but now, I've tried several times and it just won't work.

    I used this formula.
    =COUNTA(valuesbyCellColor("color", range))

  34. Very interesting segment on how to count by color and sum cells colored using conditional formatting. I'm also impressed with the great Q&A on this site.

    Here's my question: rather than design a macro that creates a message box with the count, how could I modify the code to enter the number of the count in a particular cell? For example, when using Excel 2010, if I have a row of data that contains three cells which are red due to conditional formatting, how can I modify the macro to always enter the count of these red cells into cell A8? Thanks in advance for your time!

  35. Outstanding functions. Thank you!

  36. Hi,
    the code is great it's very good work.
    but i have a small problem, how can i make it work on network.
    i have a worksheet and i need all computers in my office open it and find this macro working, when i try to open this worksheet in other computer i find the code be 'C:\Users\moataz.abdelrady\AppData\Roaming\Microsoft\AddIns\Count cells by color.xlam'!CountCellsByColor(G4:AJ4,AP4).
    thanks!!
    Moataz.

  37. This code works great! Thank you!

    Is there a way to extend the code to allow the conditional color to be in another cell?
    Rather than the color being in the cell of the number to be added, to have another cell on the same row have the color to be checked and then the corresponding number on the same row would be added to the sum.
    Does that make sense?
    The function call would be something like this:
    SumCellsByAnotherColor(, , )
    The range added by "REF COLOR RANGE" would be a column where the cells have different colors. So, say, some of them are blue. I'd want all the numbers in "number range" to be added only if the corresponding "REF COLOR RANGE" cell was blue on the same row.
    Thanks!!!
    Matt

    • Sorry, it didn't like my greater-than less-than symbols in my post.
      SumCellsByAnotherColor(numberRange, refColor, REF COLOR RANGE)

  38. I need to have red green amber indications on one my cell based on the yes no ,NA answer from 10 column result . What formula should i right for eg I need the result on cell A1. Value to be identitified is B1=yes ,C1=NA,D1=NA,E1=NA,F1=Pending ,G1=NA,H1=NA,I1=Yes,J1=YES. Could you please advice how should I write the formula.As you are aware if yes,NA and 1 no (pending) it should be amber ,if all are no then it should be red and If yes and NA it should be Green.Please advice at the earliest

  39. damet garm means thank you buddy

  40. if you did so plz notify me

  41. Sir,
    i need vba code and farmula if in column 1 for different list of name and column 2 for red color so how i cant count trough name in column 1 because there is no color
    plz help me
    Thank You

  42. Hi Svetlana

    The conditional formatting count / sum by colour has worked for me, thankyou very much

    Is there a way to have the sum total show in a cell on the worksheet?

    Regards
    Kimberley

  43. Hi the code for counting by coloured cells works perfectly. But only once. Each time I colour a new cell I have to re-enter the code. Is there any way I can get the code to update automatically. Please not I have tried the fix as described in response to comment 44.

    Please also note that I am VERY new to Macros and VBA

    Thanks

  44. Hello everyone,
    I added a little bit to the code of one of the functions, the CountCellsByColor function. I added an If, Then statement so that the function would ignore hidden rows. Here it is, in case it would be useful to another user.
    ___________________________________________________________________________

    Function CountCellsByColor(rData As Range, cellRefColor As Range) As Long
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim cntRes As Long

    Application.Volatile
    cntRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Interior.Color
    For Each cellCurrent In rData
    If indRefColor = cellCurrent.Interior.Color Then
    If cellCurrent.Rows.Hidden = False Then
    cntRes = cntRes + 1
    End If
    End If
    Next cellCurrent

    CountCellsByColor = cntRes
    End Function

  45. It's possible use the function with a named range?

  46. Thanks for the wonderful code, really appreciate the beautiful code.

    Just wanted to know the method to select multiple columns of data to calculate sum by color.

    For example,
    I need to consider data of column J, L, and N.

    Hence please let me know the corrections to be done in the formula.
    Thanks.

  47. The =CountCellsByColor worked perfectly. Thanks very much!

  48. Every time I input the CountCellsByColor function I get a compile error "For Each control variable must be Variant or Object"

  49. Hi, THank you for posting this - I've not been able to find the right UDF to count the conditional formatted cell colors. I'm new to creating UDF in the VBA editor and wanted to doublecheck - Do I need to paste the FIRST code and THEN the 2nd code directly beneath to get the full conditional formatting color count ulitization?

    thanks!

    • Hi Meg,

      To count and sum conditionally formatted cells, you don't need the first code, only the second one.

      Also, please pay attention that the second code is a macro (the first one is a UDF), and both work in modern versions of Excel 2010, 2013 and Excel 2016 only.

  50. Svetlana Cheusheva,

    Thank you for the article. It was really helpful and I do understand it all. Although, the worksheet/workbook I build utilizes conditional formatting "Formula is equal to" with relative reference not absolute and therefore returns the same value for the cells interior color even though they visually look different. I would like to count the number of cells that are equal to a particular color but after reading: http://www.cpearson.com/excel/cfcolors.htm
    the ActivCondition function will not work. Is there another way this can be done? I can provide copy of the worksheet/workbook if need be. Thank you for your help.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)