Dec
12

How to count, sum and filter cells by color in Excel 2010 and 2013

I this article you will learn how to count cells by color in Excel and get the sum of colored cells. These solutions work both for cells colored manually and with conditional formatting. You will also learn how to filter cells by several colors in Excel 2010 and 2013.

If you actively use diverse fill and font colors in your Excel worksheets to differentiate between various types of cells or values, you may want to know how many cells are highlighted in a certain color. If your cells’ values are numbers, you may also want to automatically calculate the sum of cells shaded with the same color, e.g. the sum of all red cells.

As all of us know, Microsoft Excel provides a variety of formulas for different purposes, and it would be logical to assume that there are some to count cells by color. But regrettably, there is no formula that would let us sum by color or count by color in a usual Excel worksheet.

Apart from using third-party add-ins, there is only one possible solution – utilize User Defined Functions. If you know very little about this technology or have never heard this term before, don’t be afraid, you will not have to write the code yourself. You will find the perfect code (written by our Excel guru) here and all that you will have to do is copy / paste it into your workbook.

How to count by color and sum by color in an Excel worksheet

Suppose you have a table listing your company’s orders where the cells in the Delivery column are colored based on their value – “Due in X Days” cells are orange, “Delivered” items are green and “Past Due” orders are red.

The original table with cells colored based on value.
What we want now is automatically count cells by color, i.e. calculate the number of red, green and orange cells in the worksheet. As I explained above, there is no straightforward solution to this task. But luckily we have very skilled and knowledgeable Excel gurus in our team and one of them has written the faultless code for Excel 2010 and 2013. So, move on with the 5 quick steps below and you will know the number and sum of your color cells in a few minutes.

1. Open your Excel workbook and press Alt+F11 to open Visual Basic Editor (VBE).

2. Right-click on your workbook name under “Project-VBAProject” in the right hand part of the screen, and then choose Insert > Module from the context menu.

Click Insert > Module to add a new user-defined function to your worksheet.

3. Add the following code to your worksheet:

Function GetCellColor(xlRange As Range)
    GetCellColor = xlRange.Cells(1, 1).Interior.Color
End Function

Function GetCellFontColor(xlRange As Range)
    GetCellFontColor = xlRange.Cells(1, 1).Font.Color
End Function

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
            cntRes = cntRes + 1
        End If
    Next cellCurrent

    CountCellsByColor = cntRes
End Function

Function SumCellsByColor(rData As Range, cellRefColor As Range)
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim sumRes

    Application.Volatile
    sumRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Interior.Color
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Interior.Color Then
            sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
        End If
    Next cellCurrent

    SumCellsByColor = sumRes
End Function

Function CountCellsByFontColor(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).Font.Color
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Font.Color Then
            cntRes = cntRes + 1
        End If
    Next cellCurrent

    CountCellsByFontColor = cntRes
End Function

Function SumCellsByFontColor(rData As Range, cellRefColor As Range)
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim sumRes

	Application.Volatile
    sumRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Font.Color
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Font.Color Then
            sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
        End If
    Next cellCurrent

    SumCellsByFontColor = sumRes
End Function

4. Save your workbook as “Excel 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.

5. Now that all “behind the scenes” work is done for you by the just added user-defined function, choose the cell where you want to output the results and write the following formula into it: =CountCellsByColor(F2:F14,A17) where F2:F14 is the range in which you want to count colored cells and A17 is the cell with a certain background color, a red one in our case. In a similar way you write the formula for the other colors you want to count, yellow and green in our table.
The formula to count cells by background color

If you have numerical data in colored cells (e.g. the Qty. column in our table), you can easily calculate the sum by color, i.e. get the sum of all cells colored in red, yellow and green as shown in the screenshot below:
The formula to sum cells by background color

As you see, we used this formula: =SumCellsByColor(D2:D14,A17) where D2:D14 is the range and A17 is the cell with a color pattern.

In a similar way you can count cells and sum cells’ values by font color using the formulas CountCellsByFontColor and SumCellsByFontColor, respectively.

The formulas to count and sum cells by font color

Note: If after applying the above mentioned VBA code you would need to color a few more cells manually, the sum and count of the colored cells won’t get recalculated automatically to reflect the changes. Please don’t be angry with us, this is not a bug of the code : )

In fact, it is the normal behavior of all Excel macros, VBA scripts and User-Defined Functions. The point is that all such functions are called with a change of a worksheet’s data only and Excel does not perceive changing the font color or cell color as a data change. So, after coloring cells manually, simply place the cursor to any cell and press F2 and Enter, the sum and count will get updated. The same applies to the other macros you will find further in this article.

Sum by color and count by color across the entire workbook

The VB script below was written in response to Connor’s comment (also by our Excel’s guru Alex) and does exactly what Connor requested, namely counts and sums the cells of a certain color in all worksheets of the workbook. So, here comes the code:

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

You use this macro in the same manner as the previous code and output the count and sum of the colored cells with the help of the following formulas, =WbkCountCellsByColor() and =WbkSumCellsByColor(), respectively. Simply enter either formula in any empty cell on any sheet without defining a range, specify the address of any cell of the needed color in brackets, e.g. =WbkSumCellsByColor(A1), and the formula will display the sum of all the cells shaded with the same color in your workbook.

Formulas to get a cell’s background color, font color and color code

Here you will find a summary of all the formulas we’ve used in this example as well as a couple of new ones that retrieve color codex.

Note: Please remember that all of these formulas will work only if you have added the user-defined function to your Excel workbook as we discussed earlier in the article.
Formulas to count by color:
  • CountCellsByColor– counts cells with the specified background color.In the above example, we used the following formula to count cells by color =CountCellsByColor(F2:F14,A17) where F2:F14 is the selected range and A17 is the cell with the needed background color. You can use all other formulas listed below in a similar way.
  • CountCellsByFontColor – counts cells with the specified font color.
Formulas to sum by color:
  • SumCellsByColor – calculates the sum of cells with a certain background color.
  • SumCellsByFontColor – calculates the sum of cells with a certain font color.
Formulas to get the color code:
  • GetCellFontColor – returns the color code of the font color of a cell.
  • GetCellColor – returns the color code of the background color of a cell.

The formula to get the color code

Well, counting cells based on color and getting the sum of colored cells was pretty easy, wasn’t it? Of course if you have that little VBA gem that makes the magic happen : ) But what if you do not color cells manually and rather use conditional formatting, as we discussed in these two articles How to change the background color of cells and How to change a row’s color based on cell value?

How to count by color and sum cells colored using conditional formatting

If you have applied conditional formatting to color cells based on their values and now you want to count cells by color or sum the values in colored cells, I have bad news – there is no universal user-defined function that would sum by color or count color cells and output the resulting numbers directly in the specified cells. At least, I am not aware of any such function, alas : (

Of course, you can find tons of VBA code on the Internet that attempts to do this, but all those codes (at least the examples I’ve come across, do not process conditional formatting such as “Format all cells based on their values“, “Format only top or bottom ranked values“, “Format only values that are above or below average“, “Format only unique or duplicate values“. Besides that nearly all those VBA codes have a number of specificities and limitations because of which they may not work correctly with certain workbooks or data types. All in all, you can try your luck and google for an ideal solution and if you happen to find one, please do come back and post your finding here!

The VBA code below overcomes the above mentioned limitations and works in Microsoft Excel 2010 and Excel 2013 spreadsheets with all types of condition formatting (kudos to Alex again!). As a result, it displays the number of colored cells and the sum of values in those cells, no matter which type of conditional formats are used in a sheet.

Sub SumCountByConditionalFormat()
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim cntRes As Long
    Dim sumRes
    Dim cntCells As Long
    Dim indCurCell As Long

    cntRes = 0
    sumRes = 0

    cntCells = Selection.CountLarge
    indRefColor = ActiveCell.DisplayFormat.Interior.Color

    For indCurCell = 1 To (cntCells - 1)
        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 Sub

 

How to use the code to count colored cells and sum their values

1. Add the above code to your worksheet as explained in the first example.

2. Select a range or ranges where you want to count colored cells or/and sum by color if you have numerical data.

3. Press and hold Ctrl, select one cell with the needed color, and then release the Ctrl key.

4. Press Alt+F8 to open the list of macros in your workbook.

5. Select the SumCountByConditionalFormat macro and click Run.
Running a macro to count and sum cells colored using conditional formatting

As a result, you will see the following message:
The count, sum and color code of cells colored with conditional formatting

For this example, we selected the Qty. column and got the following numbers:

  • Count is the number of the cells with a particular color, a reddish color in our case that marks “Past Due” cells.
  • Sum is the sum of values of all red cells in the Qty. column, i.e. the total number of “Past Due” items.
  • Color is the Hexadecimal color code of a selected cell, D2 in our case.

How to filter cells by color in Excel

If you want to filter the rows in your worksheet by colors in a particular column, you can use the Filter by Color option available in Excel 2010 and Excel 2013.
The Filter by Color feature in Excel 2010

The limitation of this feature is that it allows filtering by one color at a time. If you want to filter your data by two or more colours, perform the following steps:

1. Create an additional column at the end of the table or next to the column that you want to filter by, let’s name it “Filter by color“.

2. Enter the formula =GetCellColor(F2) in cell 2 of the newly added “Filter by color” column, where F is the column congaing your colored cells that you want to filter by.

3. Copy the formula across the entire “Filter by color” column.
Add the formula to get the color code.

4. Apply Excel’s AutoFilter in the usual way and then select the needed colors in the drop-down list.
Select the needed colors from the drop-down list.

As a result, you will get the following table that displays only the rows with the two colors that you selected in the “Filter by color” column.
Rows filtered by several colors in Excel 2010.

How to increase your efficiency in Excel by 50%, at the very least!

Though this section does not relate directly to counting and summing colored cells in Excel, it still may prove useful if working with Excel workbooks is a major part of your daily work. I would like to take the moment and introduce you our Ultimate Suite for Excel, a collection of 20+ add-ins specially designed to deal with the most tedious, painstaking and error-prone tasks that consume a lion’s share of your time, energy and patience.

These smart tools will help you with:

Just give them a try (a fully-functional trial version is here) and these handy tools will make common operations in Excel not only faster and easier, but actually enjoyable. If you like these tools, be sure to grab a special coupon code that created especially for our blog readers. It will give you the 15% discount on the Ultimate Suite or any separate product: AB14-BlogSpo

Hopefully, in this article you have found the information you were looking for. If not, you are welcome to post a comment and we will try to help!

92 Responses to "How to count, sum and filter cells by color in Excel 2010 and 2013"

  1. BreBre says:

    EXCEL 2020

  2. BreBre says:

    EXCEL 2010

  3. Hello BreBre,

    I’m a bit confused. Do you mean there’s a misprint somewhere in the article or it’s already time to move forward and think about Excel 2020? : )

  4. RDuncan337 says:

    Hello Svetlana,

    I really like your VB code for “How to count by color and sum by color in Excel”. I just need to take it one step further. I’m trying to use the “How to count by color and sum by color in Excel” to count the number of Red (Misses Service Levels) and Green (Achieved Service Levels) by row on a Summary tab. The Summary tab is populated by 12 Monthly tabs (Jan-13, Feb-13, Mar-13 – Dec-13) via a VLOOKUP command. Once on the Summary tab, if the Service Level was Missed, a conditional format changes the fill color to RED, if the SLA is Achieved, the fill color is GREEN. Even though the Conditional Formatting shows the cell as RED or GREEN, your VB script still believes them to be NO COLOR Fill. Any thoughts?

  5. Sam says:

    Hi, thanks for useful tutorial. I have excel data which cells contain number 1-1500 and each number colored with various background colors. I want to sum how many cells are red,yellow or blue. I’ve got visual basic editor and SumCellsByColor function but I get confused how to sum by color using that function in this case because likely different with your tutorial, is it? Can you help? :)

  6. Hi Sam,

    From your description, your task looks similar to the example described on step 5 of How to count by color. In my example I used the CountCellsByColor formula three times to count red, green and yellow cells. For example, I used =CountCellsByColor(F2:F14,A17) to count all red cells in the table. You enter this formula in some empty cell in your table where you want to output the sum, say, of all red cells and replace F2:F14 with your range of colored cells and instead of A17 enter an address of any cell colored in red. Copy the same formula in as many empty cells as many different background colors you want to count, just type a different cell address after the comma, i.e. the address of any cells with a certain background color, yellow, blue or other.

    If you want to achieve something different, please let me know or even better post a screenshot so that we can exactly understand your task.

  7. Hi RDuncan,

    Did you use the script from the Count and sum colored cells (if conditional formatting is used) section? If so, can you please send us your workbook for testing? We’ve tested that VB script on many workbooks with conditional formatting and it has always worked. Also, which Excel version do you use?

  8. Gwen says:

    Hi, Svetlana -

    I used your script and formula for a spreadsheet at work – to sum cells of certain colors for a reconciliation. It was fantastic. However, when I color additional cells for values that my reconciliation found had been missed, the related formulas do not update to reflect the correct sum. Why is that? We are working in Excel 2013.

    Thanks.

  9. Miguel says:

    THANK YOU, you saved my ass at work! ;)

  10. Hi Gwen,

    This happens because of the following specificity of Microsoft Excel. Any macros, VBA scripts etc. work on the worksheet’s level and functions are called with a change of a worksheet’s data only. Changing the font color or cell color is not perceived as the data change by Excel. So, after coloring additional cells, simply place the cursor to any cell, hit F2 and Enter, the sum of the colored cells will get recalculated.

    Hope this helps a thanks for your comment!

  11. Gwen says:

    Thank you so much, Svetlana. That worked perfectly. This function is going to be so helpful.

  12. Gwen says:

    Actually…. my cursor had to be in the cell with the formula for the F2 to work. I should have included that in my previous response for others to reference. Thanks!

  13. Connor says:

    Hey!

    The VBA for counting colored cells worked wonderfully, but I need to count coloured cells across an entire workbook not just one worksheet to have an overall count. Do you know of any way I can do this?

    Thanks!

  14. Hi Connor,

    We have just added another VB script to the article that counts and sums colored cells across the whole workbook. Hopefully, this is what you are looking for : )

  15. AbdelMonaem says:

    Thank you for your tutorial.

    I tried to use “How to count by color and sum cells colored using conditional formatting” but it did not work. When debugging there was an error at the line:

    indRefColor = ActiveCell.DisplayFormat.Interior.ColorIndex

    as the debug mentioned that indRefColor = 0

    What’s the error in my implementation ???

    Thank you
    Abdo

  16. Hi Abdo,

    We have not been able to reproduce this error. Can you please send us your workbook for testing (or any other sample workbook that reproduces the issue) and we will try to figure it out? If it is possible, please drop me a line using the contact form on my personal page.

    Thank you!

  17. lkara says:

    I tried the conditional formatting VB code and get a syntax error pointing to the first line – Dim indRefColor As Long. Any suggestions ?

  18. Svetlana says:

    Hi lkara,

    In which Excel version did you run the script? We tested it in Excel 2013 and 2010 only and it may not work correctly in earlier versions. In you are using either 2013 or 2010, please specify your version of Windows and the localization of both Windows and Excel. Thank you!

  19. Mr. Gwapo says:

    I have a problem regarding on counting of cells by color, if I merged two cells the total count will also two. Is their any additional formula to make only one count for the merged cell.. Thanks. !!!

  20. Svetlana says:

    Regrettably, it is not possible to make one count for the merged cells. More precisely, neither I nor our Excel developers know such a formula. If you happen to find a way, please do share it here : )

  21. mulugeta says:

    Dear Svetlana

    Thank you for such interesting things.
    if you have more detail in addition to excel
    please send me the coding and the note.

    God Bless you

    Mulugeta

  22. Hi Mulugeta,

    I’m glad to know my article has been of help to you. I’ve posted all the code here and you can copy it to your worksheets. Thanks for your comment!

  23. John says:

    Hi Svetlana,

    I’ve tried to use the code to do a count based on color; however, it doesn’t seem to be counting correctly since the color I have on a table is red and I am trying to count whenever I manually update it to green (it currently is counting it regardless of what color I choose).

  24. Hi John,

    Did you update a cell with the formula after changing the color manually (put the cursor in the cell, press F2 and Enter)? Please see comments 10 and 12 above for more details.

  25. John says:

    I believe I may have figured it out; it seems that the code will work for basic colors but will not work for custom colors. Thanks!

    • John,

      I consulted Alex, our Excel specialist who actually wrote the code, and he says it should work with custom colors too, except for similar colors. BTW, which code have you tried – for cells colored manually or using conditional formatting?

  26. Cheryl says:

    Hi Svetlana,
    Thanks for the code! It’s just what I was looking for. For some reason, however, it’s not counting correctly. I highlighted 42 rows: 27 black font, 15 red font and it’s giving me a count of 16 when it’s sent to count black font. I set up =CountCellsByFontColor(A3:A44,A1), where A3:A44 is the range and A1 is the color (black). Please let me know how to fix this. I would love to use this macro!

  27. Ian says:

    Hello, thanks for the sumifcolor~I am manually adjusting the colors after I have entered the formulas, but now the formula will not automatically update. I have verified the “automatic” update is checked…verified other formulas are updating automatically. However, when I add another cell to the color I want to update my sum, it does not do so unless I click on the formula and hit enter.

    Please advise…and thanks!

  28. Francisco Silvestre says:

    Doesn’t work. A window oppens giving the information of error in formula formatting. Any body could help?

    Thanks

  29. Boyo says:

    Hi Svetlana,

    The code works amazingly and I thank you.
    Could you please assist me in with a code of how to count merged colored cells?

  30. Hi Boyo,

    Regrettably, we do not know a way to make only one count for the merged cells. Our developers say this is a very tricky with a lot of pitfalls.

  31. Matt says:

    Hi,

    Thanks for the code. It’s very useful. I am looking for code that counts coloured cells and then recounts when a filter is applied to those cells. i.e. it counts all the coloured cells in the filtered data. Any help with this would be much appreciated.

    Matt

    • Hi Matt,

      I think we might be able to help. Please specify which exactly code you need – the first one that works with cells colored manually or the other one that supports conditional formatting.

      Also, do you use Excel’s auto filter or do you filter your data in some other way?

      • Matt says:

        Thanks for the response.

        Coloured manually – The data was originally conditionally formatted however I copied and pasted it to remove the conditional formatting so the data is effectively coloured manually.

        I am using Excels autofilter.

        Matt

        • Matt,

          Try to find the following line in the code:
          If indRefColor = cellCurrent.Interior.ColorIndex Then

          And replace it with:
          If (indRefColor = cellCurrent.Interior.ColorIndex) AND (False = cellCurrent.Rows.Hidden) Then

          This line is used both in “Function CountCellsByColor” and “Function SumCellsByColor”. You can change it in both functions if you want to count and sum the filtered cells, or in the CountCellsByColor function only.

          Does it work now as you want it?

  32. Josh says:

    Thank you for adding the code in response to Connor’s comment about the entire workbook. I am trying to get the sum, not the count for the workbook. I just want to make sure that code works for the sum as well as the count, and if so, what data range would I use? Thank you.

    • Hi Josh,

      It’s a very good question! It actually made me realize that the formulas we published in that section were not true.

      When using the function that counts and sums colored cells across all the sheets in a workbook, you need to use the following formulas to count and sum, respectively:
      =WbkCountCellsByColor(A1) and =WbkSumCellsByColor(A1)

      We’ve slightly modified the code and does work for the sum as well as for the count now. Just enter the second formula in any empty cell on any sheet without defining a range, specify the address of any cell of the needed color (it’s (A1) in the example above) and the formula will display the sum of all the cells shaded in this color in your workbook.

      Thanks again for your comment and your great question!

  33. Jake says:

    Hello,
    Thanks for the code. It is working but i have a question of how to set up a rule to use it. I used the Conditional formatting code.and now i am trying make a rule to count the number of green cells automatically for each column and return it to a specific cell. each column is a month and i want to know how many red green, yellow, and orange cells there were for each month. if you could shed some ligght that would be great.

  34. Hello Jake,

    Regrettably, none of our Excel developers knows how to do this. The Excel Object model does not allow using the needed property in formulas, which is why it’s impossible to create a conditional formatting rule to count the colored cells. Calling a macro manually as described in the article is the only possible way that we know.

  35. jraju says:

    Hi, by chance i get this article while surfing. excellent piece of information. How to count cells colour to denote a separate colour. For example, if there are five yellow,five red and 10 oranges in a row, say from a1 to a20. in A21, i want to stipulate a condition, where, there should be 5yellow (not less or more), similarly 5 red and 10 oranges, if these are satisfied, then I want a21 to be green colour otherwise, blank. How to effect it in a selected row and then i can copy the formula.May i expect your reply as solution.

    • Hi Jraju,

      You can add this function to your workbook and then enter the following formula to A21:
      =AND(CountCellsByColor($A$1:$A$20,$B$1)=5, CountCellsByColor($A$1:$A$20,$B$2)=10, CountCellsByColor($A$1:$A$20,$B$3)=5)=TRUE

      Where $B$1, $B$2 and $B$3 are the cells with your color patterns (yellow, red and orange, respectively).

      If the cells with color patterns are on another sheet, then you need to add the sheet’s name, e.g.
      =AND(CountCellsByColor($A$1:$A$20,ColorLookup!$A$1)=5, CountCellsByColor($A$1:$A$20,ColorLookup!$A$2)=10, CountCellsByColor($A$1:$A$20,ColorLookup!$A$3)=5)=TRUE

  36. Greg says:

    Thanks to you I am able to correctly count cells of a particular colour now in a column, but I really need to be able to count only the rows where both a colour is in a certain column and another has the words “Indoor” in it (i.e. 2 conditions to be met to count the row).
    I tried the formula: =COUNTIFS(Report.xls!$I:$I,$B$9, Report.xls!$AH:$AH,”(CellsByColor(Report.xls!$AH:$AH,A3))”) which appears to work but always results in the answer zero. Can you see where I am going wrong please or suggest a better way?

    • Hi Greg,

      There is no Excel formula that would let you count rows based on two different conditions at a time. This can be done by creating an additional column in your Report.xls sheet. If such an approach is acceptable for you, please let me know and we will try to work out a formula.

  37. Wanda Rolon says:

    I have an assignment that requires counting color blocks per wall (total), per column (total).

    Column South West 1 – Example: F,6 (DB-Dark Blue), G,6 (LG-Light Green), H,6 (A-Amber)
    Row 2
    Type B10

    The Whole point is calculating quantity of color glass per column/wall (4colums per wall).

    Thank you in advance.

    In kind,

    Wanda Rolon

  38. Hi Wanda,

    Sorry, I am not sure I can exactly follow you. If you can send us (at support@ablebits.com) your original workseet and the result you are trying to achive, we will try to help.

  39. Tammy says:

    Thank you for providing this code. It has worked well. My worksheet includes colours with a pattern (which represents pending) and I do not want to count these cells.
    Is it possible to add to the code so it does not count the colour if a pattern is also in that cell?
    Thank you
    Tammy

    • Hi Tammy,

      In theory, this can probably be done. But to be able to say with certainty, we need to have a look at the colors you are using because very close colors may not be recognized by the script. So, if you can send us your workbook (without real data, only colors) and indicate which color(s) is a pattern, we will try to help.

  40. Yohan says:

    Hi Svetlana,

    Thank you very much for this piece of code, it helped me a lot!
    However, I wonder if there is small bug inside or not
    (I don’t know if my “pre” tags with SyntaxHighlighter below will work sorry for the inconvenience)

    The function “GetCellColor” returns “.Color” property :

    Function GetCellColor(xlRange As Range)
    GetCellColor = xlRange.Cells(1, 1).Interior.Color
    End Function

    but the other functions compare the values of “.ColorIndex” properties:

    indRefColor = cellRefColor.Cells(1, 1).Interior.ColorIndex
    For Each cellCurrent In rData
    If indRefColor = cellCurrent.Interior.ColorIndex Then
    cntRes = cntRes + 1
    End If
    Next cellCurrent

    Shouldn’t we always compare “.Color” properties ?

    Yohan

    • Hi Yohan,
      This is a very good question! The code is correct. The point is that the .Color property returns the color RGB code, e.g. FFFF00 for yellow; while .ColorIndex returns the color index in Excel’s color palette. That is why we use .Color when we need to know what color is used in a certain cell. When we are comparing two colors, we use .ColorIndex as a quicker way.

  41. Gary Hanna says:

    Hi Svetlana,

    I am facing an issue with specific colors like dark green and dark red, the formula is mixing both of them, so if i have a dark green cell with value of 5 and another dark red with a value of 7 so in this case the formula is giving me 12 as a result for both colors , need your help please

    • Hi Gary,

      It’s hard to say what the cause of the problem is without seeing your colors. Can you please send us your worksheet (only colors, no data is actually needed) at support@ablebits.com? We will try to figure it out and fix the code if needed.

    • Alexander says:

      Hello Gary,

      Thank you very much for your workbook. You are absolutely right, this is Excel’s bug. I’ve tried to optimized the code performance, which is why I used the .ColorIndex property. As it turned out it still has a bug – earlier Excel versions did not allow using more than 56 colors in one workbook. So, 56 is the maximum number that the function can return. In newer Excel versions beginning with Excel 2007, you can use millions of colors at a time. But the .ColorIndex function still returns up to 56 colors even for new colors.

      I’ve re-written the code using the .Color property that returns a full color code. Please copy / paste it anew, it should work well now.

      Thanks again for your worksheet, it gave me a clue!

  42. Jim says:

    I can’t seem to use these functions without getting a #NAME error.

    • Hi Jim,

      Excel throws this error when it cannot find GetCellColor’s functions. The most obvious reasons are:
      - The functions have not been added
      - They were added to a wrong location
      - The workbook was not saved as macro-enabled
      - There’s some misprint in the function’s names

      Will you, please, verify the items above?

      • Jim says:

        Thanks for the response Svetlana.

        My mistake was trying to call the Function from a hidden macro enabled workbook located in XLSTART while I was in a .xlsx workbook.

  43. Paul says:

    Thanks for the code, your instructions are very clear and it works brilliant.
    However I did experience a problem when copying the code from the website I found it pasted without any line breaks or indents. I was using ie9 but when I changed to chrome everything worked ok. Thought I’d just say in case anyone has similar problems.

  44. Darcy says:

    Thanks for the code. It works beautifully. I am wondering if there is a way to have the sum update automatically when you colour a new cell. I know you said above that it is not possible, but there must be some code that could be written that would allow you to be able to do it.

    I’m not very familiar with VBAs so I am not sure where to start. But couldn’t you write a code of some sort that says:

    If “colour change” in “range” then “Run Function”

    Just wondering.

    • Hello Darcy,

      We have found the solution on the page below and updated our script: https://sites.google.com/site/e90e50fx/home/sum-by-color-calculate-when-color-changed

      However, please not that this script works a bit slower. If it is not critical for you, proceed with the following steps:

      1. Download the updated code from the post.

      2. Create a new Class module and add the following code to it:

      Public WithEvents cX As CommandBars
      
      Private Sub cX_OnUpdate()
        Static s As String
        Static l As Long
        If s = ActiveCell.Address(, , , True) Then
          If l <> ActiveCell.Interior.ColorIndex Then
                ThisWorkbook.ActiveSheet.Calculate
          End If
        End If
        s = ActiveCell.Address(, , , True)
        l = ActiveCell.Interior.ColorIndex
      End Sub
      

      3. In the module with our code, add the following code to the very beginning before the current code.

      Dim cf As New Class1
      
      Sub Auto_Open()
          Set cf.cX = Application.CommandBars
      End Sub
      
      Sub Auto_Close()
          Set cf.cX = Nothing
      End Sub
      

      4. For the changes to take into effect, close the workbook and open it anew.

  45. R says:

    Hi, I am using the macro for cells that are conditionally formatted. Is there a way to produce the result in a cell versus a message box?

    Thank you for this information!

  46. RyanRo says:

    Hello, every time i attempt to use the sum by color function it says sum function not defined.

    My intended use is to mark a sales order size in red to indicate that it is complete. I do not want red in the total because this is primarily used for ordering more product and I do not want to over order. Thank you for any help!

  47. tim says:

    This worked great! However, I want to do something a bit different and cannot find a solution. I just want to count any colored cell in a row. My rows are dates. The columns represent projects. The colored cells represent different tasks for these projects and the dates they are to be done. These tasks are color coded. I want to limit the concurrent tasks so I want to count number of color-coded tasks. I don’t care how many yellow, how many blue, etc. I just want to know how many colored. For example, if there are three yellow, two blue and three orange, I want to see the number 8. Any ideas?

    Thanks,

    • Hello Tim,

      You can achieve the desired result by counting the non-colored cells (white is also a color : ) and then subtracting this number from cells total. So, add the code to your workbook as described in the article and copy the following formula in the last column of each row, starting from row 2:
      =COLUMN()-1-CountCellsByColor(A2:Q2,R2)

      R2 – the cell with the formula, it should always be non-colored.

      A2:Q2 – color-coded columns that you want to count. If your color coded cells start from any column other than A, than replace -1 in the formula with the corresponding column number. E.g. if your color codes start with column B, the formula will be =COLUMN()-2-CountCellsByColor(B2:Q2,R2)

  48. sheallakay says:

    very useful thanks much. Even though i dont try all as yet.

  49. Cassy says:

    It worked great but when I closed the page and opened it again, it gave me an error and the module was gone. I saved so i am confused on what is happening. Please help!

  50. khaled says:

    thanks for the great support.. but i lose the function when i close excel and reopen it again.. any solution for that?

  51. khaled says:

    PS.. I have saved it as macro-enabled workbook

  52. Cassy says:

    Now, I am getting #NAME? in the boxes where my formula =CountCellsByColor(N102:N334,A335) was entered. Please Help!

  53. John says:

    I would like to write a single IF statement to print a “1″ or a “2″ in the cell if the colorindex=3. I tried something like =If(range(“a1″).colorindex=3,1,2)) but something isnt quite correct, I could do the VB code, but i really want something really simple like the IF statement since I am only looking a single cell. I dont want to use “conditional formatting”, the color of the cell is set via VB code (selection.colorindex=3)
    Thanks

    • Dmitry says:

      Hi John,

      Excel’s IF function as well as all other functions work with simple expressions and standard Excel formulas only. You cannot use any VBA code inside the IF statement. Also, as far as I remember none of standard Excel functions can return color index. And this was the reason why custom VBA code is needed for this task.

  54. sarah says:

    Hello,

    Thank you for sharing the instructions and code. I’m experiencing some issues with the implementation. When trying to open and run the macro using function Alt F8, the macro does not appear. When I search for the Macro Enabled Workbook file (.xlsm) that I’ve saved, I see that there is an exclamation mark on the file icon. When attempting to open the .xlsm file, I receive the following error: “Compile Error: Invalid outside of procedure” Could you please help me to figure out what I’m doing wrong? Thank you!

    • Hi Sarah,

      Please verify whether you copied the entire code from the web-site (from our experience, occasionally the first or last line is missing). If it is not the case, try to add the code anew to some other worksheet. If the error persists, please specify what Excel version you are using.

  55. Sofi says:

    Hi,
    Svetlana, you are a genius in excel. I like your vb code.
    Thank you.

  56. Brad says:

    I am trying to do a bit of a hybrid so I can use an if function. My goal is to make it so if a user manually colors a cell red I can then run an if statement on it, when they remove the red it would no longer be true and can run a different formula. I have added and additional column that I will hide when done, I would like to have a way to use the background color in a single cell in a column give me the color index or some sort of color in column b that I could then run a test against. so if reds color index is 4 and cell A1 is red, then I would like 4 or something simular to show in column b. I am sure this is really simple but over my head. I tried adding the first part of the getcellcolor function but it returns the same color for the cell regardless of what color the background is. I would like to be able to do the same thing with font colors.

    I am using excel 2010 and 2013

    Thanks

    • Brad says:

      OK, so I just figured out my problem and it was me, I have it working now. Is there any way to run a macro that will automatically do the f2 part? I am trying to hide the column that has my color index in it so users can’t mess it up but now they can’t hit f2 and calculate it.

      Thanks, brilliant script

      • Hi Brad,

        With the current (updated) version of the code, your users can press F2 on any cell, not necessarily the one with the formula.
        Moreover, you can do without F2, by using a solution I posted in response to comment 44 above. Hopefully, this will be helpful for you too.

Post a comment