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

797 comments

  1. Much appreciated. I'm finding the coding her very helpful.

  2. Hello All,

    it was worked if count by number but not if it count by date. could you please help me on this.

    appreciated for your support.

  3. Hello. Thanks a lot for the codes. I am wondering whether it could be possible to use the function for conditionally formatted cells in a cell as a general function to be inserted in whatever cell (not as a macro in a dialog box). I.e. I am looking for adjusting the function for summing colored cells which would work for conditionally formatted cells as well. Any ideas about adjusting the code?
    Thanks.
    Stanley S

      • Hi Alexander! Below, in January Svetlana Cheusheva wrote a reply that there is only a macro available for both sum&count. But directly under this comment I am writing now I already asked you about some possible way of adjusting your code for function from "count" to "sum". Is it possible?
        Thanks!
        Have a nice day,
        Stanley

        • Hello!
          Try this code:

          Public Function SumByColorCells(CellRange As Range, TargetCell As Range)

          Dim TargetColor As Long, SumCells As Long, C As Range

          TargetColor = Evaluate("cfcolour(" & TargetCell.Address & ")")
          For Each C In CellRange
          If Evaluate("Cfcolour(" & C.Address & ")") = TargetColor Then SumCells = C + SumCells
          Next C
          SumByColorCells = SumCells
          End Function

  4. SIR,

    I AM NOT GET COUNT OF COLORS WHERE CONDITIONAL FORMATTING APPLIED BY ABOVE MENTIONED SOLUTIONS/

    KIDNLY HELP

    • Hello!
      This custom function counts all cells shaded by a certain color. Including with conditional formatting.

      Public Function CountByColorCells(CellRange As Range, TargetCell As Range)
      Dim TargetColor As Long, Count As Long, C As Range
      TargetColor = Evaluate("cfcolour(" & TargetCell.Address & ")")
      For Each C In CellRange
      If Evaluate("Cfcolour(" & C.Address & ")") = TargetColor Then Count = Count + 1
      Next C
      CountByColorCells = Count
      End Function

      Function CFColour(Cl As Range) As Double
      CFColour = Cl.DisplayFormat.Interior.Color
      End Function

      • Hi Alexander!
        Thank you for solving the case. Do you have any ideas on summing the range of cells including conditional formatted cells? Replacing "count" with "sum" in your code simply does not help but I do not know why...
        Thank you!
        Stanley S.

  5. I am using CountCellsByColor. Can I count cells that also contains a certain text? It counts the cell colors fine, but other users are highlighting other things in the same colors which are now giving wrong calculations. eg cells that are yellow, but also contain 'test' for example.

  6. This was really useful, I was trying to do an analysis and the way the analysis was done was by color. But I wanted to count some colors and wanted to do the "if" statement, is there a way I can do that?

    • Hi!
      Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail, I’ll try to suggest a solution.

  7. Nice code.

    Appears to be a small typo

    VBA macro to count and some conditionally formatted cells.
    Sub SumCountByConditionalFormat()

    ... and SUM conditionally ...

    • Hi Ron,

      Oops, a silly typo indeed. Fixed, thank you for pointing that out!

  8. Your code for "How to sum by color in Excel" is excellent with only one issue, it doesn't automatically recalculate if you color the cells after setting the the calcuation cell. Forcing the recalculate works well and my sheet is set to automatic recalculation but it will not. I assume closing and re-opening will also cause the recalc.

    Any help appreciated.

  9. Hello,

    I was able to use SumCountByConditionalFormat just fine as a sub however i was wondering if there is a way to make it a function? so instead of preselecting and answering a msgbox i could place the information in the formula bar?

    Thank you,

    Halle

  10. I really like the function. I am wondering If there is a way to make it refresh on color change. in other words, I use it to track tasks. So when I complete the task change the color of the cell. If I use the paintbrush it works but if I just apply the new color it does not update until I click into the cell and click out.

  11. After add this module in excel, it need to loading everytime when I edit the excel, may I know how to solve this problem?

  12. I added in your VBA for Count by color across entire workbook and saved it as a macro enabled excel file, i created a new tab and filled a cell with the colour i wanted to contain cell A1 i then used the formula =WbkCountByColor(A1) in the adjacent cell. the following error was then generated: Microsoft visual basic Compile error: Sub or function not defined. Seems to relate to text in the code CountCellsbyColor? not sure how to remedy the situation?

    Thanks

    • Hi!
      For this function to work, the code for the CountCellsByColor and SumCellsByColor functions must also be added to Excel. Read the Note carefully after the function code.

      • Thanks, I got it to work and finished the workbook, however after closing and reopening the file in order to get the function to work it now reads VBAProject.Module2.CountCellsByColor(b2:b8, b2)
        Any idea why this has changed as now the formulas don't work and I'll manually have to edit them all to get the functionality back?

  13. Is there a function for the macro "Count and sum conditionally formatted cells" ?

    I need the number provided as an output in a cell that I can leverage with other formulas elsewhere.

    • Hi Eric,

      Nope. For conditionally formatted cells, we only have a macro.

  14. Great resource! I used the Sumbycolour function and it just WORKED through!

    Appreciate the good work. Thanks a lot!

  15. I can get it to work when I place the macro in the specific workbook I am working on, but cannot get it to work when I save it in the Personal WB via VBA. Any thoughts? I create new WB's from a program each week and need it to work from the personal macro wb vs having to re-add it every week to the new wb. At that point it it not useful. :(

  16. Hello! I used the CountCellsByColor VBA code and it worked like a charm. Only one issue, it is not automatically updating - I have to hit calculate now. I have made sure the Automatic Calculate option is turned on, but is not working. Any quick fix suggestions?

    Thank you!

  17. hi,

    I have used your code in desktop successfully but when I upload into the server it shows " Security Risk - Microsoft has blocked marcos from running because the source of this file untrusted"

    Kindly assists with thanks.

  18. I'm using =CountCellsByColor(C4:AG4,AK3) and it did work well when I have applied all to spread sheet,but after reopening Excel workbook I got everywhere NAME ERROR. What I'm doing wrong?

  19. I also tried this, but it didn't work.
    =SUMPRODUCT(IFERROR(--(MOD($D$39:$L$47,1)=0),0),SumCellsByColor($D$39:$L$47,D9))

    Where D9 is the BASE color and
    $D$39:$L$47 is the range with different colors, integers, and decimals, which I am trying to SUM only the integers of the D9 color.

    Thank you very much for your time
    Best regards
    Drago

      • Thank you very much for your quick response.

        Unfortunately, I get a #Value error. I copy and paste your solution, then try ctrl+shift+enter to activate it, but get a #value error.

        But we managed to do it by adding 2 lines in the macro code for the SumCellsByColor

        If IsNumeric(cellCurrent) Then

        If Int(cellCurrent) = cellCurrent Then

        Thank you so much for this article. It will save me a lot of time in the future.

        Best regards
        Drago

        p.s If someone is interested in the whole macro for this particular case, I could paste the whole code in the comment section.

  20. Hello,
    Thank you very much for this. It works like magic.
    I have a small problem though. I need to sum ONLY the integer values from the cell range and to exclude the decimal numbers.
    I have a cell range with integer and decimal numbers and I use the =SumCellsByColor($D$39:$L$47,D10) formula and I need the SUM of ONLY the whole nubers.

    Thank you very much

    Best regards
    Drago

  21. I got a problem to sum the amount in the green box. The thing is, whenever the checkbox ticked (true) then the cell contain an amount will turn to green (next to checkbox cell). I want to make the sum formula only for the green boxes for total revenue from January to December (horizontal data).

  22. Its really great using this solution.

    It resolved my problem which could have taken weeks time for me to calculate the colored cells.

    Thank You very much.

  23. Your examples above are either (1) sum or count by color, or (2) sum or count by font. They are great and helpful. But I am just wondering if there is a formula to perform [count by color + font] and [sum by color + font] ?

    Thanks

  24. Is there any way to make the cells auto-calculate when applicable cells are formatted?

    • Hello!
      Formulas are automatically recalculated when the cell value changes, but not the format. Your problem can be solved with a VBA macro.

  25. It does wonders. But I am still not able to execute the sum part of it. The value keeps coming to 0. No problem with the count function though. I will be grateful for the help.

  26. How can the macro can be modified if I only want to count or sum the colored cell after filter the table? My apologies, still much a novice at VBA.

  27. You are a goddess! This is incredible. Thanks! I got all the functionality to get this done.

  28. Thanks so much from Pematang Siantar City Indonesia.

  29. Worked perfect

  30. I have a table that lists all shipments not yet invoiced. I have colored all the rows using conditional formatting. As an example, shipments that are overdue for invoicing are colored red, based on more than 5 days past the arrival date of the vessel. We have several managers responsible for different types of commodities, e.g. bulk oils and petroleum, frozen foods, chemicals, etc.
    Can the CountIfs count how many reds, blues, greens, and yellows by manager? Do you have a code that can do this for me? At the moment I am counting manually.
    I have tried =CountCellsByColor(DBN!$B$6:$B$1299,SUMMARY!$C$3) but this results in total blue cells, not how many blue cells a particular manager has.

      • Is there any VBA code for counting colored celled which are conditionally formatted?? i did not find solution anywhere for latest excel versions

        • Hi!
          Have you tried the ways described in this blog post? Why don't you want to use the VBA code that is suggested in the first paragraph of the article?

  31. LOVE this code - thanks so much!

    In my sheet I have dates in Row 7
    And data in the rows under it

    I have a start date in Cell E4, and a end date in column d starting at D8

    I want to only sumbycolour when the dates are equal to or inbetween the 2 specified dates

    So Start Date is 12/11/21
    End Date is 16/11/21

    The row with the data i'm counting has data in it for the whole year

    I feel like I need to include an IF statement - but getting lost - appreciate anyones help!

      • Thanks that helped heaps.... so I now have the SUMIFS working, and have it written like this:

        =SUMIFS(F5:Z5,F4:Z4,">="&B1,F4:Z4,"<="&C5)

        Where I have dates in B1 and in C5

        Is there a way to incorporate this with the sumbycolor - maybe a sumbycolorifs?

        I want the above to only sum when cells are of a specified colour.

        Thanks again

        • Hello!
          You want to sum by both date and cell color. But these two conditions can be applied using different functions. Unfortunately, you can only combine them if you add VBA code to the SUMBYCOLOR function.
          You can filter by dates on your table and then use the SUM BY COLOR tool. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

          • Thanks again - unfortunately due to other restraints in the workbook / I don't have the ability to use filtering for this.

            Anyone have the knowledge / skill required to write the code required to make sum by date and cell colour work together? Would so appreciate it!

            • Hi @Alexander Trifuntov - any chance you can help with the code for this?

              • Hi!
                Unfortunately, we are not in the business of writing VBA code. However, the filter is a basic Excel tool. I don't really understand why you can't use it.

  32. Hi
    I tried to use the function but every time I am getting #name error. What should I do?

  33. Good morning,

    I've tried the macro but it keeps giving me zero as result both for sum and count :(
    HELP!

    Thx,
    Mara

  34. Amazing!

    This is exactly what I needed.

    Thank you for sharing :)

  35. Excellent article and it worked perfectly! Thank you!! Well done!

  36. Many thanks, very useful reference, worked like a charm.

    More power & best regards.

  37. This is great! The only problem I've having is that the cell color doesn't get counted unless I use the format painter tool to copy the color from the cell in the formula. It also isn't working for conditional formatted cell colors. Any help is appreciated!

  38. I am trying to collect data that is in certain color boxes on my spreadsheet to total up amounts of hours someone has done over a certain amount of time. I am pulling my hair out trying to find a solution. Can you please help before i go bald lol.

    • My spreadsheet is a register so i need to add up different color boxes. I have 6 different colors for the aims that i need to add up. So with one color (blue) it is looking for how many hours that have completed over a 43 week programme. There is two things that i want to do. I want to add the amount of blue boxes there are but i also need to know the total of hours they have completed over this time.

      • Hello!
        Pay attention to the following paragraph of the article above - How to count by color and sum by color in an Excel worksheet. What problems did you encounter while using this instruction?

        • A friend of mine managed to do it in excel but when doing it on google drive sheet its not working

          =ColorFunction(K5,C14:V56,TRUE) this is the one she used.

          It keeps coming up with error unknown function

          • Hello!
            These guidelines apply only to Excel. If you need advice on Google Sheets, search for an answer or ask a question in the related section on our blog. Try it here.

  39. I have an Excel sheet that I am trying to use as a temporary scheduling tool. It is set up to look like a gantt chart with the colors set with conditional formatting based on how long each step in the scheduled process will take to accomplish. I'm trying to figure out how to count the times each conditionally formatted color shows up in a weekly bucket to calculate my labor needs. Is there a way to amend your VBA to read just a portion of my table? Or any ideas on how to accomplish this?

  40. Thank you so much for the count by color and sum cells colored using conditional formatting. It's my problem but simple it has an error in something. When I format some spreadsheets with conditional to color. Then use the formula CountCellsByColor(range, color code). The result is wrong. So I hope have good news about it

  41. It works but it only does one row. When I try to copy the formula down it doesn't work. HELP!

  42. Thank you so much, it was really helpful!

  43. I was trying few times failed until found your tips, it is working well, thank you so much for your sharing

  44. I have been helping my wife with some spreadsheets. Everything was going great until she needed to count employees and the new new employees were typed in red font and were not counted in the total number of employees. Your CountCellsByFontColor worked perfect.

    Thank you very much!!!

  45. This was awesome and so easy to implement. Thank you so much!!!

  46. now it is working thanks

  47. i need to count multi color in big range contains of about 1000 cells could you help please

  48. I tried the countcellsbycolor formula, it just plain did not work! i followed all instructions right & got the #NAME? dumb error over 7 over again.

    • I got the same result, pressed F9, and it worked.

  49. Excel by color coding

  50. Thank You so much. This is really useful.

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