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, Excel 2013 and Excel 2016.
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.
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, 2013 and 2016. 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.
- Open your Excel workbook and press Alt+F11 to open Visual Basic Editor (VBE).
- 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.
- Add the following code to your worksheet:
Function GetCellColor(xlRange As Range) Dim indRow, indColumn As Long Dim arResults() Application.Volatile If xlRange Is Nothing Then Set xlRange = Application.ThisCell End If If xlRange.Count > 1 Then ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count) For indRow = 1 To xlRange.Rows.Count For indColumn = 1 To xlRange.Columns.Count arResults(indRow, indColumn) = xlRange(indRow, indColumn).Interior.Color Next Next GetCellColor = arResults Else GetCellColor = xlRange.Interior.Color End If End Function Function GetCellFontColor(xlRange As Range) Dim indRow, indColumn As Long Dim arResults() Application.Volatile If xlRange Is Nothing Then Set xlRange = Application.ThisCell End If If xlRange.Count > 1 Then ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count) For indRow = 1 To xlRange.Rows.Count For indColumn = 1 To xlRange.Columns.Count arResults(indRow, indColumn) = xlRange(indRow, indColumn).Font.Color Next Next GetCellFontColor = arResults Else GetCellFontColor = xlRange.Font.Color End If 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 - 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.
- 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 enter the CountCellsByColor function into it:
CountCellsByColor(range, color code)In this example, we use the formula
=CountCellsByColor(F2:F14,A17)where F2:F14 is the range containing color-coded cells you want to count 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.
If you have numerical data in colored cells (e.g. the Qty. column in our table), you can add up the values based on a certain color by using an analogous SumCellsByColor function:
SumCellsByColor(range, color code)
As demonstrated in the screenshot above, we used the 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
CountCellsByFontColorandSumCellsByFontColorfunctions, respectively.
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.
Custom functions to get a cell's background color, font color and color code
Here you will find a summary of all the functions we've used in this example as well as a couple of new ones that retrieve color codes.
Functions to count by color:
CountCellsByColor(range, color code)- 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(range, color code)- counts cells with the specified font color.
Formulas to sum by color:
SumCellsByColor(range, color code)- calculates the sum of cells with a certain background color.SumCellsByFontColor(range, color code)- calculates the sum of cells with a certain font color.
Formulas to get the color code:
GetCellFontColor(cell)- returns the color code of the font color of a specified cell.GetCellColor(cell)- returns the color code of the background color of a specified cell.
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, Excel 2013 and Excel 2016 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
- Add the above code to your worksheet as explained in the first example.
- Select a range or ranges where you want to count colored cells or/and sum by color if you have numerical data.
- Press and hold Ctrl, select one cell with the needed color, and then release the Ctrl key.
- Press Alt+F8 to open the list of macros in your workbook.
- Select the SumCountByConditionalFormat macro and click Run.
As a result, you will see the following message:
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.
Sample workbook for download
If you have any difficulties with adding the scripts to your Excel workbooks, such as compilation errors, formulas not working and so on, please download this sample workbook with the CountCellsByColor and SumCellsByColor functions ready for use and try them on your data.
Fastest way to count and sum cells by color in Excel - new!
Updated on 1-Jul-2014.
When we published this article, we hoped it would be popular because we used to get a lot of questions about how to count and sum cells by color in Excel. But even in our wildest expectations, we did not think it was going to be that popular! What you see in comments on this page is just a tiny portion of the enormous feedback we have received. So, our team decided to take a step further and create an Excel add-in that would count and sum cells by the color you specify or by all colors in the selected range.
Let me introduce you our brand new add-in - Count & Sum by Color for Excel 2016, 2013, Excel 2010, 2007 and 2003. Once installed, it will place 2 buttons onto the Ablebits Data tab on your Excel ribbon - One Color and All Colors, as you can see in the screenshot below:
Count and sum cells by the selected color
You simply click the One Color button on the ribbon and have the Count & Sum by Color pane open at the left of the worksheet. On the pane, you select:
- The range where you want to count and sum the cells
- Any color-coded cell
- Color option - either background or font color
Once done, click Calculate and see the result in the lower part of the pane straight away! Apart from the count and sum, the add-in calculates the average and finds the max and min values. No macros, no formulas, no pain :)
Count and sum cells by all colors in the selected range
The All Colors option works basically in the same way, except that you do not have to choose the color. In the "Show results for" section, you can select any of the following options: Count, Sum, Average, Maximum or Minimum value.
If you want to copy the results to your worksheet, click the Paste results... button at the bottom of the Count & Sum by Color pane.
And here is a list of the main features you will find in the Count & Sum by Color add-in:
- Count and sum cells by color in all versions of Excel 2016, 2013, 2010, 2007 and 2003.
- Handle cells color-coded manually and with conditional formatting.
- Besides counting and calculating the sum, the following functions are available: Average, Maximum and Mininum values.
- An option to recalculate colored cells automatically when a new range is selected.
- Copy and paste the results to any location on the current spreadsheet or to any other sheet.
You can download a trial version of the Count & Sum by Color add-in here. Hopefully you will like it, but we are keen to know your feedback, anyway : )
How can I get the Count & Sum by Color add-in?
Currently the add-in is available as part of the Ultimate Suite for Excel. This is a collection of our best tools especially designed to deal with the most tedious, painstaking and error-prone tasks in Excel.
In addition to the Count & Sum by Color add-in, the Ultimate Suite includes about 40 other tools that can help you to:
- Merge data from different tables
- Remove duplicates
- Combine duplicate rows into one
- Merge cells, rows and columns
- Find and replace in all workbooks
- Clean data in worksheets
- And much more!
If you like the tools, be sure to benefit from a special 15% off coupon code that we provide especially for our blog readers: 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!
EXCEL 2020
EXCEL 2010
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? : )
What you have been demonstrating here is Excel 2020. Awesome piece of code !!!
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?
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? :)
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.
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?
Yes I can send it to you. It is a pretty large sheet that does many vlookups to gather the data. All data is within the spreadsheet. The tab (worksheet) I am working on is called "SD SLA (ALL)". I am trying to count all the failed (RED) cells and the non-failed (no color) cells. I am using Excel 2010. Is there an email address where I could send you the file directly?
Thank you
Ray
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.
THANK YOU, you saved my ass at work! ;)
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!
i am working in a company as a MIS Executive but i have some problem in vba coding can you help or can you give the some traning session of vba coding me
Sorry, we do not provide training services. But I am sure there are plenty such courses on the web.
Thank you so much, Svetlana. That worked perfectly. This function is going to be so helpful.
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!
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!
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 : )
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
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!
Hello,
Had the same problem like Abdel - any luck with the debug?
I tried the conditional formatting VB code and get a syntax error pointing to the first line - Dim indRefColor As Long. Any suggestions ?
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!
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. !!!
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 : )
could you add something like this to the code?
Sub Count_Merged()
Dim rng As Range, cell As Range, cell1 As Range
Dim lngMERGE As Long
Dim lngMERGEAREA As Long
Dim ws As Worksheet
Dim wb As Workbook
Dim vari As Variant
Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet2")
lngMERGE = 0
ws.Select
With ws
Set rng = Range(.Cells(1, 1), .Cells(100, 9))
For Each cell In rng
If cell.MergeCells = True Then
lngMERGE = lngMERGE + 1
lngMERGEAREA = lngMERGEAREA + cell.MergeArea.Count
Set cell1 = cell
'Debug.Print cell1.Address
End If
Next cell
End With
vari = lngMERGEAREA / lngMERGE
vari = lngMERGE / vari
MsgBox "Number of cells merged and counted as one instance is: " & vari
End Sub
that counts the cells where cell.merge is true and then adds the cell.mergearea.count values. then it divides the cell.mergearea.count by the cell.merge is true count AND finally divides the cell.merge is true value by the previous value.
lol
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
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!
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).
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.
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?
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!
Hi Svetlana, I deleted the coding and started again and it works now. Must have been my error. Thanks so much!
Hi Cheryl,
It's great that you've figured it out on your own! And thank you for letting me know.
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!
Hi Ian,
This is how all macros and VBA scripts work in Excel, you do need to click on the formula and press Enter after changing the colors manually. Please see my comment 10 above for more details. Thanks for your comment!
Thanks! Your site is going to make me look wicked smart. And I appreciate you.
Doesn't work. A window oppens giving the information of error in formula formatting. Any body could help?
Thanks
Hi Francisco,
What Excel version do you use? Please note that the script works with Excel 2010 and 2013 only.
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?
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.
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?
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?
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!
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.
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.
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
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.
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
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.
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.
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.
Ok !
in fact I needed to compare RGB codes, so now evreything is clear
Thank you for your response
Hi Svetlana Cheusheva how are you i am new qamar
i want to ask something
i am doing job as computer operator
i make result of 8 semesters each semester is come after 6 months BS.Engineering Degree is being given to students in 4 years
Questions:
1- I post summer result in some students record because they some subject
i want when i post their summer result then changing effect shoud be on all semester result,
2- but before knowing it is important that how can we merge more than one result combine or all results how will be in link
If you understand my words then kindly give me reply and solve my problems I will be very thankful to you for this kind act
Naveed
Hello! Thank you very much for the code! I just added it to my worksheet and it works like a charm (I have also purchased the Suite). One question related to Yohan's: by adding the first VSB module, GetCellColor gives me the MS access color code. I take this is the same as 'Excel's color palette'. Correct?
If so, question: How do I get the RGB code as output instead of the MS code? And where do I have to put the piece of code in the module above (sorry I am a newbie)
Another question: how do I search for a single specific cell in a specific color across my worksheet? The input should be the MS code, while the output should be the location of the cell.
Thank you!
Vanina
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.
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!
Thank you both Alex and Svetlana for your responses & assistance :)
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?
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.
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.
Hi Paul,
Thank you very much for letting us know!
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 Sub3. 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 Sub4. For the changes to take into effect, close the workbook and open it anew.
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!
Unfortunately, this cannot be done using a macro because of some limitations of Excel. We are planning to create an add-in that will cope with this. Please stay tuned!
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!
Hi RyanRo,
This happens because Excel cannot find some or all of GetCellColor’s functions. Please check the most frequent causes I listed in my reply to comment 42 above.
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)
You're the greatest, thanks!
very useful thanks much. Even though i dont try all as yet.
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!
Hi Cassy,
Please make sure you save the workbook as macro-enabled and add the functions to the correct placement.
thanks for the great support.. but i lose the function when i close excel and reopen it again.. any solution for that?
PS.. I have saved it as macro-enabled workbook
Hi Khaled,
The only possible reason that I can think of is that you accidentally added the code to a module of some other workbook. Will you check that please?
Now, I am getting #NAME? in the boxes where my formula =CountCellsByColor(N102:N334,A335) was entered. Please Help!
Hi Cassy,
It's hard to say what exactly causes the error without seeing your workbook. If possible, you can send it to us at support@ablebits.com and I'm sure we'll be able to fixed.
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
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.
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.
Hi,
Svetlana, you are a genius in excel. I like your vb code.
Thank you.
Thank you very much Sofi!
But all accolades should really go to our Excel guru Alex : ) As I mentioned in the article he wrote the code for this example.
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
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.
hello,
thanks for the help. i am trying to use the count by conditional formatting. it is working when a cell's background is colored, but does not work when formatting is for border color.
also, instead of the count popping up in a box, i would like to have it in another cell.
could you please help me?
thanks,
shachi
Hello Shachi,
All of the functions count cells by the background color only, the border color is not considered. We are not sure that counting by the border color is feasible, this requires additional research.
As for the pop-up, it is displayed in case you color cells using conditional formatting. Otherwise, you can use the CountCellsByColor function that outputs the result directly in a cell.
Hi Svetlana,
Thanks for the above code but i guess if we have some unique data in cell along with the color code then there is no need of VBA coding as we can apply simple countif or sumif functions to count/sum the data.
Hi Puran,
You are absolutely right. This code is purposed for the cases when there is no obvious data / color code relation.
Hi Svetlana,
I am using this this functions and its great "How to count by color and sum cells colored using conditional formatting", but i need to go an extra step and actually publish the count in a designated cell. How can i do this?
Thanks,
Tatiana
Hi Tatiana,
This is not possible with the current script. But we are going to release the Excel add-in soon that will have this feature. Please stay tuned for the update.
Hello,
I have tried to cut and paste the code from your tutorial
How to count, sum and filter cells by color in Excel 2010 and 2013.
When I do, it shows up red in the module and utilizes 2 lines
it does not appear like yours.
I have cut / pasted other codes and they worked but this one doesn't. Can you tell me if Im missing something on this one?
maybe I cannot accomplish what I want -
1. I have a column of numbers (cell A5 - A434) some of those cells w/in that range are colored yellow.
2. on line A435 - I want to sum ONLY the yellow cells.
Is your tutorial "How to count by color and sum by color in an Excel worksheet"
the correct one to utilize?
if so, I am having trouble with your code (when I copy/paste) - it copies red and utilizes 2 lines.
I have copied/pasted other codes and they appear to paste as is.
Thank you for your guidance
Angel
Hi Angel,
Yes, this script can help you to achieve the desired result. Try copying the code to Notepad first and then copy/paste from Notepad to Excel.
Alternatively, you can copy it directly form the blog in some other browser.
If this does not work anyway, please send your sample workbook at alexander.frolov@ablebits.com and we will figure this out.
Hi Svetlana,
I got it to work. Thank you. I figured out that you did not need to run the code, just save it and then continue on with the formula.
Thanks
Is it possible to SUM the amounts in a colored cell? Not how many of a particular color but for example - I have 7 yellow cells - each has different amounts in there... I want an end result.
I was able to finally get my other problem resolved so I thank you dearly!
Hi Angel,
Please use the SumCellsByColor function instead. It does exactly what you need - sums the values of colored cells.
Hi,
I am sure this is a questions for a different topic but hoping you can help? I have a list of 20 individuals and I want to create a 4 quadrant pie chart and place them in the region their scores qualify them to be in the pie. Can this be done?
Thanks,
Tatiana
Hi Tatiana,
You can do this via a pivot chart or CountIfs function. This sample workbook demonstrates both solutions. Hopefully, this is what you are looking for.
How can i plot the Name within the chart?
Sorry, I do not know a way. You can ask this question on excelforum.com or mrexcel.com forums.
I'm running into an issue where both the =CountCellsByFontColor(F3:F12,F3)and the =CountCellsByFontColor(G3:G12,G3)formulas return ALL of the cells with conditional formatting (standard red/yellow/green)rather than the ones matching the reference cell.
Any idea why this could be happening. Also, I may have missed where someone else pointed this out, but your code above does not copy/paste cleanly into VB, as the line breaks aren't recognized. Takes a bit of manual tweaking. Not sure if this is a limitation of your site or something that can be fixed.
Great solution to a common problem (if I can get it to work anyway), by the way.
Hello Dave,
The CountCellsByFontColor function does not work with conditional formatting, it is purposed only for the cells colored manually. Please use this code instead.
As for the code formatting problem, most likely it is caused by a particular internet browser. Can you please specify which exactly browser you are using so that we can test on our side? And thanks a lot for your feedback!
Hey i love the code that counts the selected cells while using a conditional formatting. I just wanted to know is there a function that does the same thing. i have a file that constantly updates depending on the drop down selection. i want a function that counts the number of highlighted cells when the conditional format highlights them. Some type of count function that works with conditional formatting and updates based off of the drop down that i select. if you understand what i am saying.
Thanks again for the help.
Hey Joe!
Currently we know only one way to do this - using a macro. Other ways are not possible because of Excel's limitations. But we are working on this...
The straight count by colour worked perfectly thanks, once I'd read one of the queries and copied to a Word document first. Saves me all the pain I've had of having an extra column with a letter to match the colour and then sorting and counting from that. Cheers!
Hi! I'm trying to count by color and i've added the code in the worksheet and saved it as a excel macro-enabled workbook. But when i try to enter the formula i get the messege:"The formula you typed contains an error" I'm using Excel 2010, any idea what i'm doing wrong?
Hi Anders,
Please let me know which formula are you using. Could you copy/paste it here, exactly as you are entering it in your spreadsheet, with all cell references?
The result I get adds the totals of all the cells in the row that are being conditionally formatted. I have 3 different conditional formats, I understood that the count of sum function would only act on those that met the format of the cell I wanted summed or counted. What am I doing incorrectly or does the formula not act on the specific formatting of the cells in question? I can send the file to you if you'd like to view it. Thank you.
Hi Jerry,
The =CountCellsByColor() and =SumCellsByColor functions count only cells of a specified color. For example, =CountCellsByColor(F2:F14,A17) will color the cells in the range F2:F14 that are of the same color as cell A17. If it does not work in this way for you, please do send us your workbook at support@ablebits.com.
This code works great....I could use one additional function. I need to check and see if a cell has data in it before I count it.
Thank you Dennis, please let me know if I can help in any way.
Hi
I posted the 3 modules you provided in my workbook. I saved it as a .xlsm file type. When I try to enter the formula - GetCellColor or CoutCellsBy Color - it brings me back to my modules coded with the error:
Compile error:
Expected:end of statement
I don't know how to fix this.
Thanks for the help!
When it brings me back to the module I inserted it highlights the very first GetCellColor in the module.
Thanks again!
Hi Janis,
Please make sure that you pasted the complete code in the module. The compile error usually indicates that some part of the code is missing. Please verify that the code starts with "Sub" and ends with " End sub". You can also download this sample workbook with the CountCellsByColor and SumtCellsByColor functions ready for use and try them on your data.
Good Morning,
Is there a way to count by background color only if it is prior to a specific date?
I have a very large spreadsheet with several columns, see example below. Each month I need to count active Companies(color coded by location) for example; in January column, only count Companies that are blue and active, in February column only those that are blue and active, etc. Then repeat for Companies coded purple.
I was able to use your code above to add by color, but now I need to eliminate those that have terminated.
Company Group # Eff Date Term Date January February March April
---------------------------------------------------------------------------
ABC Co. 123 1/1/10 1/1/14 $0 $0 $0 $0
123 Co. 456 4/1/09 $20 $20 $20 $20
D's Bar 789 2/1/07 2/1/14 $15 $0 $0 $0
PJ's Pub 654 1/1/14 $45 $45 $45 $45
Pizza Pub 321 1/1/13 $10 $10 $10 $10
Day Center 852 1/1/12 3/1/14 $25 $25 $0 $0
ABC, 123, D's Bar are Blue - therefore result in January s/b 2, February s/b 1, March s/b 1.
Pj's Pub, Pizza Pub, Day Center are purple - therefore result in Jan s/b 3, Feb s/b 3, March s/b 2.
Is there a way in January to countif blue and term date is after 12/31/13? Then in February countif blue and term date is after to 1/31/14?
I need to read by date rather than $0 as sometimes commission is received even though terminated.
Thank you in advance for any help you can provide, as I am very new at this.
Hello Wendy,
Yes it is possible. Please send us a sample workbook at alex@ablebits.com, and we will work out the solution.
I have a similar situation; is there any way you can publish and example of how to do this?
In my case, the sheet isn't very big, I'm just trying to automate things as much as possible so that data can be updated when I'm not there to adjust things. I need to be able to sum all cells of a certain color that are adjacent to cells containing specific text.
(I have a series of quantities in column A, and quarters - Q1 2016, Q2 2016, etc. - in column B. I need to sum only those quantities that occur next to a specific quarter, and are of a specific color.)
My chart did not post as set up.
Company/Group #/Eff Date/Term Date/Jan/Feb/March/April (each is a column)
Thanks for the Article -
The spreadsheet I have contains both manual and conditional formatting in the same column,to provide same color - Red background color in this case - now the code that is written addresses either one of the two cases - ie either you can do a manual count or count by conditional formatting; is there a code that combines both cases in one so it can work in my situation.
Also, do you know if there is a way to subtotal that can provide accurate count if the spreadsheet is filtered based on a certain criteria;
Thanks for checking.
-Samir
Hello Samir,
The Conditional Formatting code also counts and sums cells colored manually. As for subtotal, sorry we do have any idea.
Hi Svetlana, I found your article very helpful. I am also looking for a way to subtotal the count of cells by color for sheets that require filtering. Is there any update on this? Thanks in advance!
This helped me a lot.. thanks..
just one more thing on "How to count by color and sum cells colored using conditional formatting" - is there a way this can only count the filtered values? (i.e.. just count the QTY only for the PAST DUE on the Delivery column)
here's an example.. when i filter "1" it still counts the hidden cells... i just want it to only count "red" in number "1"
A B C D Number
red green orange 1
orange red green 2
orange red green 3
red green orange 1
orange red green 2
red green orange 3
sorry - im new to excel :|
here's an example - i wanted it to count only "red" when Number is filtered to "1"
A B C D Number
red green orange 1
orange red green 2
orange red green 3
red green orange 1
orange red green 2
red green orange 3
i believe this can be achieved by selecting the range manually but is there a way to do this without doing it manually?
Hi Dniens,
Our code counts all cells in the selected range, including filtered and hidden cells. To be able to tell you with certainty if we can help you achieve the result you want, we need to see your data. If you can send us (alex@ablebits.com) your sample workbook and the desired result,we will try to help.
Thanks for the prompt response. It worked. Hopefully, the add in that you / Aex is developing will work in both scenarios. Keep up the good work guys. Thanks
Hi Samir,
Our new add-in, Count & Sum by Color, is finished and available for download. And yes, it works in both scenarios and in all versions of Excel!
This is genius. However, would vlookup and hlookup by cell colour be possible. For example, =vlookup([CellColour],table_array, column_index_num, [range_lookup])?
Hello Wazza,
You need to add only one function to your workbook – GetCellColor. Then add an additional column to your lookup table, this should be the first, i.e. left-most column, and copy the following formula to it: =GetCellColor(F2). F2 is the needed cell color.
An then, you use this formula =vlookup(GetCellColor(A1).... where A1 is the color-coded cell.
Or better still - how can I build a sumif formula around coloured cells. For example =sumif(range, [CellColour], [sum_range])?
You can use the SumCellsByColor function to sum the values of cells of a given color in the selected range. Or, are you looking for something different?
Hi Svetlana,
Yes, looking for something a bit different, the cells that contain the values I want to sum are in a different row to the coloured cells so would be looking to do a calculation similar to the sumif function
Cheers
Hi Wazza,
Thank you for the clarification, I understand now. Regrettably, this is not possible because our functions do not support array formulas.
Many thanks for this article. The instructions, code and functionality were excellent and exactly what I was looking for.
Great stuff! Exactly what I was looking for...
Thank you so much!
So confused about the "How to count by color and sum cells colored using conditional formatting" needing to be a sub and not a function. Not sure I understand what the limitation is. I am still playing with it. Works perfectly as a sub but need to get the count in a cell.
Hello Shelli,
Regrettably, there is no easy way to implement this as a function. I mean the function that would correctly handle all possible variations of conditional formatting. This would require a great deal of VBA code, tedious debugging and testing against different Excel versions and various Office localizations. (That is why the other solution you've tried works in simple scenarios and does not work with complex conditional formatting).
So, our Excel specialists have weighed the pros and cons and created the Count & Sum add-in, which caters for all possible scenarios and works correctly with all Excel versions and all kinds of conditional formatting. You can download the evaluation version and try it out on your data.
Just to follow up on my previous comment. I had already found http://www.get-digital-help.com/2013/10/02/counting-conditionally-formatted-cells-vba/ which works great in their example. We have pretty complicated conditional formatting on multiple cells. If I choose more than a row or a column or have a cell in the range that does not have the conditional formatting, I get a #Value. I do not really understand why theirs works as a function, but in a limited fashion, and yours works all the time as a sub...
Hello Shelli,
I have not tested their code, but it looks like it works only with conditional formatting rules based on formulas that changed the cell's background color, if true. I am not sure it will work with color scales. This code should also be checked with regard to min/max/duplicates.
Also, the code implies that each conditional formatting rule found in the selected range applies to ALL selected cells. If it is not so, then the code will return #Value or even throw some VBA execution error.
Hi!
Thank you for this script! I had problems when I tried to use it on Excel 2010. Excel said that it isn't valid formula and it has some errors.
But then I replaced "," --> ";" and it worked (like this: =SumCellsByColor(D2:D14;A17).
Hi!
Most likely this is because you have the List Separator set to ";" in your Windows Regional Settings, so you should use a semicolon in all of your Excel formulas.
Hi Svetlana,
I thank you for the amazing tool. It is working well.
I wanted to find out how do i count colored merged cells. I am creating a dashboard for data.
Can you please help me.
I thank you
Hi Arshad,
We have not tested the code on merged cells, but in theory it should work properly, i.e. a merged cell shall be calculated as one cell. Does function behave differently on your data?
hi
how to protect the particular column and cell in the excel 2007
i dont want whole sheet protect only particular cell i want
Hi Sindhoor,
You can find a way in this article:
http://office.microsoft.com/en-001/excel-help/lock-cells-in-a-worksheet-HA010342979.aspx
Great stuff! Saved me a lot of time!!
Thanks a ton.
I have implemented your script and the patch to actively update the cell data and it works perfectly. When I reference that data in another cell it doesn't update the data without me forcing the update. By the way Ctrl+Alt+F9 will update the cells. No need to click in the cells to accomplish the data refresh. Is there another line I can add to the script to keep the referenced data active as well?
Hi Brian,
Can you please send your sample workbook to Alex (alex@ablebits.com) and describe the use case step-by-step. In this way it will be easier for us to reproduce the scenario and suggest a fix.
Is there a way to count cells of a specific color and containing a specific text? Say the name Bill appears on a sheet 10 times and 5 of the cells have been manually colored red. Is there a way to count how many times cells containing the text "Bill" and background filled red?
=COUNTIFS(A1:L326, A330, A1:L326, getcellcolor(A330))
Tried this and got "0" when, in fact there were 2 that met the criteria.
A330 is a text value of a person's name as well as being filled with the reference color. I have tried it with "text value of 330" and "=text value of A330".
Hi Brian,
Yes, this is possible now, we've updated the code of the functions. Please copy the updated code to your workbook and then enter the following array formula (remember to press Ctrl + Shift + Enter to complete it):
=SUM((A1:B13="Bill")*(GetCellColor(A1:B13)=GetCellColor(D1)))
Where A1:B13 is the range where you want to count the occurrence of the word "Bill", and
D1 is the helper cell with the color sample, e.g. red.
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.
I just tried this macro and it works well. thanks you
Hi, I used the VBA code and formula to count colored cells. I have 6 colors. Four of them calculate correctly, but 2 do not (extra cells are counted). What can I do to correct the error?
Thank you.
Hi Kayla,
It is difficult to say why this happens without seeing your data. Please send your workbook at support@ablebits.com and we'll look into the issue. Also, please check if the specified range doesn't include the cell that you chose as a color sample.
Thank you, I've just emailed Support.
Hi Kayla,
Thank you for your worksheet.
The point is that your worksheet contains some merged cells, and our formulas count each individual cell that was merged.
hello there, wondering if I can get some assistance. I want to be able to add all "points" from each location in one colum, however, each month the place of each location will very, so i want to be able to get a formula where it can be added no matter where the location number is at.
this is kind of an example of data am working with.
Location Quota Points Location Quota Points LocationPoints
4014 141.00% 20 4006 46.00% 20 4006
4022 141.00% 16 4016 44.00% 16 4016
4003 130.00% 15 4022 43.00% 15 4003
4016 127.00% 14 4014 42.00% 14 4014
4006 126.00% 13 4003 41.00% 13 4022
is there a way to figure add this up with out having to arrange them in least to greater?
Hello Fabiola,
It is difficult to understand your task because your data are distorted in the comment. If you can send us your workbook at support@ablebits.com and include the result you want to achieve, we'll try to work our a proper formula.
Thank you for this code! It is great. I see it only works when I have the macro open even though I saved it as you advised. Is there anyway to make this a permanent function so that I won't have to open the macro every time?
thanks!
This code has worked wonderfully, thank you! I only have one issue. When I saved the workbook, I saved it as a macro-enabled workbook as you instructed. I have opened the spreadsheet, and the macros are enabled because the formulas all work correctly. However...
There is a graph in my workbook, and now - when I click on the chart - it has an error that says "Cannot run the macro "name of chart_Click'. The macro may not be available in this workbook or all macros may be disabled." Do you know why I would get this error?
Thank you!
This code was a god send... It worked wonderfully and has sped up the time it takes to complete a report I have to do each month by at least 50%
Thanks :)
This code has worked wonderfully.
Thank you!
I can't get the macro to show when I press Alt+F8. I copied the code multiple times.
Hello Jay,
Make sure that the macro starts with Sub SumCountByConditionalFormat() and your workbook is saved as a macro-enabled workbook. If it doesn't work for you, please download the workbook with code examples described in this post:
https://cdn.ablebits.com/_img-blog/count-color/count-sum-color-sample.xlsm
Hello,
First off, I love this code. I am brand new to macros and this walk-through helped me out tremendously, so thank you! I would like to use this macro with another formula, if possible. Here is what I am looking for:
I am comparing data across multiple sheets based on the color of the cells to indicate what sales stage the contract is in as well as their expiration dates. For the report I'm trying to create, I have columns in place to show which contracts will expire 120 days from today, 90 days from today, 60 days from today, and 30 days from today. The rows indicate the sales stage that the contract is in. The columns are based on an IF statement using TODAY as the volatile factor and the rows are based on the lovely macro you have created, using each cell color as the indicator of which specific sales stage the contract is in. Since the first row indicates that the contract is in a Not Contacted state, I would like to have each row below that not only count up the number of cells that have a specific expiration date and background color, but also subtract from the Not Contacted row.
I feel like this would require an IF statement within the macro, however I am so new to macros that I am not really sure where to begin with this one. If my explanation is confusing at all, I apologize. Any and all help is appreciated!
Thanks,
Antionette
Really a handy tool. Saved a lot of time :-)
Thanks for sharing it!
Excellent code and even better follow up assistance, from what I can see above at least.
I a using the SumCellsByColor command code to prepare a timesheet for employees working on different jobs using the colors to sum the different 'types' of time (Normal, OT, Onshore, Offshore). I have individual tabs assigned for each employee and am using a separate 'summary' tab to bring all the information together. It is easy to prepare a summary for each employee and the type of time (using your code). However I also want to track the time spent on individual job numbers (job numbers are marked as lines on each tab with the columns being dates).
Basically, as it is possible that each job is worked on by separate employees, I want to be able to show what type of time is being spent on each job number on my 'summary' tab.
Can you make any sense of that? I think I may have even confused myself! haha!
Any assistance is much appreciated!
Thanks & Regards,
David
The Code is useful but when I saved it as xlsm format and reopen it the next day the Calculation is gone. I would need to add in the code again and recalculate again.
Please advise if it can be saved in the document itself?
Thanks and regards,
Rebecca
Rebecca,
When you open it up just press enable macro again and it recalulates
Nige
Hi This Article is really usefull and saved a day to me..
Thanks for posting this...
Hi!
I love this function, you did a very good job coming up with it!
I am having an issue though.. I am using the first code you posted to count the number of cells coloured a certain shade in a specific column. It works great, until I change a cell's colour and the number in the function cell doesn't change -- I need to go to that cell and run the code again to see the up-to-date sum.
Is this what you mean by 'manually' ?
Can I fix this?
Thank you!
Hi Elle!
By default the code doesn't track coloring of new cells. If you need to automatically update the result as soon as you color another cell, please use the Count & Sum by Color tool:
https://www.ablebits.com/excel-count-sum-color/index.php
Alternatively, you can use the code posted in response to comment #44.
Hello!
Thanks for this amazing code! I copied and pasted it in, but when I try and use the formula it just gives me the following ...#NAME?
Can you tell me where I'm going wrong?
Hi Laura,
Got the same problem. I ensured that I saved the file with macros enabled (xlsm format via File | Save As...) and then in the IDE (Alt+F11), I ensured that the functions were part of my workbook (they were originally in PERSONAL.XLSB and possibly not in my file.xlsm). Finally, I saved the file and was then able to use the functions. Hope this helps you.
The functions take a while to process as little as 1024 fields in a row with 7 different colors but it works great!
Marc
Thanks so much for this! Saved me much time and effort! Have a great day! :)
Marc
Amazing functions,
but i have a query please:
it's a little bit strange question, but how it can works on columns basis, to be more clear if you merge 4 cells horizontally it will show only 1!!!
Very good stuff, does it work on conditional format cell? I have tried but failed to do so.
Hi Svetlana
Thanks for sharing wonderful code, i just used it and saved a lot of time.
Thanks again!
Hi Svetlana,
Fantastic and elegant code. Many thanks. Some people are just TOO clever. It's so great to have free access to such gems. Very generous.
Best regards,
Bobbler
Thank you very much for the count by colour (if manually applied) code. Works brilliant and has really helped me in my job.
Hi,
I'm using Conditional Formatting to color my Cells. So, when I use the "GetCellColor", they call have the same value (5296274).
So, in my Conditional Formatting, I have cells turn red if the date in a different cell is over 1 year and green if it's under one year.
Is there any way to use the "CountCellbyColor" for Conditional Formatting?
Thanks,
Bruce
Hi Bruce,
Regrettably, there is no reliable way to get a cell's color in a custom function when this color was applied using conditional formatting.
Hi
have just used this in one document. However I get the error message "ambiguos name detected count cells by color". What have I done or not done?
many thanks L
Your VBA is just what I've been looking for. However, I'm having a recurring "crashing" problem with Excel 2013. The code has been inserted as a Module verbatim from your site. I don't have to press F2 to refresh the values even tho lots of posts say I should. The VBA seems to recalculate my counts whenever I manually update the cell fill color. But it runs very, very slow. I can see the processor demand running in the bottom info bar (4 processors - running slow). Any clues as to why this would be running so slow? I'm running 64-bit Windows 7 with 8GB RAM. Is the VBA having trouble with the 64-bit OS perhaps?
hello,
It was very helpful,
thank you
Other than sum, count...
Can I identify the max value in a range of colored cells in my row?
(my green cell is an "achieved" date. I need the latest achieved date in my row...multiple other cells are of other colors)
*Manually formatted cells
thanks!
Hello, my question above might not have been clear..
My example
row: 1-Jan, 2-Jan, 3-Jan, 4-Jan 5-Jan
1-Jan and 2-Jan are highlighted green. The rest of the dates have no highlight.
I want to look at only the green dates and find the latest (Max).
(so 2-Jan)
Thank you for any guidance on this!
I know this has been mentioned before (so I've tried doing my own research) but when I paste this I can't find it when I go to run it. I've created some basic ones to check that it's not something wrong with my excel and they all work fine.
Can't seem to find anyone with a solution to this, but clearly it must be me as it's working for everyone else! any suggestions? using excel 2010
Many thanks
Thank you
hello,
how to count non-blank colored cells???
Thank you so much! Have not programmed in years and this was an easy solution to an otherwise very complicated and time-consuming task!
Hello.
Great post - I have one problem though. When i use this code in my worksheet, it only works if i give my cells a color directly. If i use Coditional Formatting, on my cells, and use this to change the colors, it does not work. Is there anyway where i can use the color generated by Coditional Formatting, instead of using the generic color which would be white.
Please Help me! :D
Read the whole article, or just concentrate on the section titled "Count and sum colored cells (if conditional formatting is used)" https://www.ablebits.com/office-addins-blog/2013/12/12/count-sort-by-color-excel/#count-conditional-formatting-color
Many Thanks
Its working!
Hi, I have used the above code in the VBA editor to enable the =CountCellsByColor code. When I use the code written as well as the formula provided it comes back with a 'NAME' error. I was hoping I could send my workbook for you to have a look at! Thanks :)
Hi Svetlana,
Thanks so much for this work!
Just one thing though. Is it possible to count cells that are of a certain colour but also contain a cell entry?
Where there are empty cells I still get a returned value based on the pre formatting.
Also it cause an issue with Merged cells as if 4 cells are merged together you get the returned value of 4!
Thanks in advance
Lee
Hi again Svetlana,
I have made the changes to suit the comments for Brian's comments above and it works great for SPECIFIC text. However if I change the search for a specific text to the search for any text, as in "*" then it returns nothing.
Any help would be appreciated
Thanks
Lee
ALL sorted...thanks anyway!
I have a spread sheet with few coloured cells across different columns ( Red a, amber and green )
If i had to see only red coloured one what is the formulae
Thanks for the info, worked very well. How can I transfer the same macro to other workbooks?
Kudos to you all.
Same question as Vicki asked on 24.02.2015. How can we transfer the VB code for all other Workbooks?
Excellent work, thank you very much to share something really helpful and interesting too. :)
I have Excel 2013 and cannot figure out how to post the code. When I click on Alt-F11 I do not see Visual Basic Editor--instead, a box appears on top of the workbook with pictures of everything that is currently open on my desktop. If I choose and click on the picture of the workbook that I'm trying to make changes to, it just then opens as the normal workbook. I want to both count the number of cells that are colored, and separately sum the amounts based on the various colors. Is Excel 2013 handled differently, and if so, can you help?
Absolutely fantastic, have wanted to be able to do this for years (rather sadly I know). Great to discover this resource, all signed up now.
I am trying to count the number of times cells in a contiguous range match a certain color AND contain certain text. I'm using this for a football offensive play script where I highlight a cell yellow if that position is getting the ball on a particular play, and type the name of that player in the cell. I'm trying to calculate how many times each player is getting the ball based on the criteria that the cell would be filled yellow and have their name in it. I've been trying to use a combination of your code plus a countifs, but can't get it to work.
Hi
I want to be able to summarise data in cells located in diverse places on the workbook; ie not in a single table or column or row etc.
Perhaps if I could tag or mark the cells containing the relevant data I want to summarise so that the cell which displays the summary output would look for all relevantly marked cells in the workbook and treat them as if they were all in a single column or row or table.
Is something like this possible?
Thanks for posting this, the instructions work well and make it very simple to implement. Worked perfectly in my spreadsheet!
Svetlana,
I do not understand why the sub SumCountByConditionalFormat() can't be rewritten to a function.
I did it (stubbornly trying) and -of course, as you explained- it did not work.
The function just halts on the command
indRefColor = refCell.Cells(1, 1).DisplayFormat.Interior.Color
I mean, really halts, no further commands are executed.
No error message, nothing, the function just halts and the cell where i put the function call gets "#VALUE!".
Why is this?
Why does .DisplayFormat.Interior.Color work on "ActiveCell" but not on "refCell.Cells(1, 1)"?
And why no error message?
Hello Hans,
The point is that the DisplayFormat property appeared in Excel 2010 only and it cannot be used in User-Defined functions. Please see the following page for more info:
https://msdn.microsoft.com/en-us/library/office/ff838814.aspx
And yes, it does return the #VALUE error and no other clues :(
Thank you very much, this is great
Hi
I have used your code to count by color and sum cells colored using conditional formatting and it seems to work great.
However, Is it possible to use a similar code to put the result of count or sum in a designated cell rather than in a pop up box.
Thanks,
Thank you for your answer.
Is this the still the case in Excel 2013?
If yes, let's hope Microsoft will fix this.
I am afraid it is. I consulted Alex (our Excel guru who wrote these UDFs) and he says Microsoft actually promised to fix this. So, let's hope :)
Works perfectly! Thank you!
Thank you!
Works perfect and helped me a lot!
Good job!
Hi,
I'm trying to use your CountCellsByColor code and seems to work fine when I enter the color in the cells manually, but I have one small problem. It seems to be designed to measure only manually entered colors within the cells, and in my particular case I have multiple values in the columns, which are highlighted in different colors when repeated (by using the conditional formatting - Highlight cells that contain text option) - so technically speaking, the color values are automatically populated and not manually, therefore the formula doesn't quite count them correctly. Any advice on what I should do here?
Please help. I have different sheets with staff name verticl and horizontal 31 colomns and cells in all different colors, I want to create a final sheet that will count colors
You guys are friggin' awesome! Thank you for posting this and the related "Change the row color based on cell value" article. So helpful.
After everything we have done....wen i run the coding it says compile error : syntax error....y so..?
Thanks a Lot it helped me
Hi there! I have a color-coded table and need help. The table has a person listed for each row and various categories for the columns. I need to count how many people have a green-colored cell in any column next to their name, but if they have more that one green it should only count once. Additionally, if they have at least one green cell in their row, then these people should be ignored when tallying the number of people with at least one blue or yellow in their row . How would I do this?
Thanks. This was exactly what I needed in terms of utility, explanation, and how-to steps. I very much appreciate this.
Sincerely,
Tom
Hello there! In need of some help... Can I combine the countcellsbycolor function with a countifs function? I would like to only count if a cell as a certain text and is colored. Any help?
Hi Cristina,
To count cells with several conditions including the cell's color, you will need to use the GetCellColor function as part of an array SUM formula. Please see my response to comment 89 for a formula example.
What a waste of time. It doesn't work.
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.
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.
Every time I input the CountCellsByColor function I get a compile error "For Each control variable must be Variant or Object"
The =CountCellsByColor worked perfectly. Thanks very much!
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.
It's possible use the function with a named range?
Hi Cristhian,
Yes, it works with named ranges.
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
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
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
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
if you did so plz notify me
damet garm means thank you buddy
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
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)
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.
Outstanding functions. Thank you!
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!
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))
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.
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).
Hello,
For us to be able to help you, please send a small sample table with your data in Excel and include the result you need to get to support@ablebits.com. Thank you.
this was so helpful :D thank you!
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.
Hello, Linto,
For us to be able to help you, please send a small sample table with your data in Excel and include the result you need to get to support@ablebits.com. Thank you.
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.
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.
This is really great work. For me it worked like a charm.
Thanks, this worked great.
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.
Hi,
This code is not working along with Conditional Formatting.
Pls suggest the alternate solution.
BR//Deepak
Hello, Deepak,
Please have a look at this article, it has VBA that works for Conditional Formatting:
http://www.get-digital-help.com/2013/10/02/counting-conditionally-formatted-cells-vba/
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.
The code is not saved after exit
tanks.
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
Hello,
Thank you for your comment.
For us to be able to help you better, please send a small sample table with your data in Excel and the result you expect to get to support@ablebits.com
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!
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/2013/12/12/count-sort-by-color-excel/#comment-52201
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?
Oh, never mind; found what I need here: https://www.ablebits.com/office-addins-blog/2015/02/25/array-formulas-functions-excel/#howto-enter-array-formula
What a great site this is! My toolbox just doubled in size!
Thank you very much for your kind words, Lisa. Though we can't guarantee a quick reply due to a big volume of comments we get, feel free to ask any questions you have if anything remains unclear.
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.
Hey Martin, would be great if you could help me for above query....
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.
Great job Svetlana on this "How to count and sum cells by color in Excel 2010 and 2013"
when i use this code its gives me a error
Compile error:
Syntax error
(Module1 48:0)
Hello Singh,
You can download and use this sample workbook with the CountCellsByColor and SumCellsByColor functions ready for use and try them on your data.
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.
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?
Hello Rohit,
Unfortunately there is no sure way to get the conditional formatting color with the help of user-defined functions, please see the section "How to count by color and sum cells colored using conditional formatting" for more details.
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.
Hi Ramki,
Thank you very much for your reply!
Rohit, this is one of the ways to sum or count the number of cells with a certain color set by conditional formatting rules.
The second way would be using the VBA script that you can find in the section How to count by color and sum cells colored using conditional formatting.
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.
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
hi there...when i save the file as a micro enabled xls file, the formula returns a #name? error....
Thoughts? Please help
Thanks
=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!
Hi William,
This error means the code structure is broken.
Please try to remove all the code and copy it again.
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.
Hi Pat,
To help you better, we need a sample table with your data in Excel and the result you want to get. You can email it to support@ablebits.com. Please add the link to this article and your comment number.
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)
Amazing! I've tried and it works great . thank u ^^
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....
Hi naveen,
Sorry, the DisplayFormat property does not work in user defined functions.
Please read the following article:
https://msdn.microsoft.com/en-us/library/office/ff838814.aspx
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.
Hi,
Just wanted to say thanks so much for sharing this awesome code.
Excel is already an amazing tool and people like you just make it better.
Thanks,
Brett
Thanks for the code .. it works great !!
Great Stuff. Thanks for sharing.
Thanks for sharing, I have certain merged cells to count in between the range. is there a solution yet for that
I see that the code works for everybody but me, so it must be me, but would still like know why it doesn't work. The error on the worksheet in cell F2 is #Name?. I first hand copied from the web site the code, then hand typed it in, still the same error. Then in cell F2 , I hand typed the formula, that was said to type. =CountCellsByColor(A1:A10,E2), where E2 is the cell that has the color I'am trying to count. I work on Excel 2010, and Windows 7. Looking for how many yellow cells there are.
Please ignore my previous post I finally figured out my blunders.
Hi,
Am running Macro with cells that have both hyperlinks to server as well as conditional formatting of them cells. Have copied the codes from above script (CountCellsByColor in addition to the Conditional Formatting code later in the page) and they fail to capture the cells which are conditionally formatted, returning a result of '0'.
Moving the data selection area outwith the hyperlink/formatted area returns valid sum data just cant seem to get the Conditionally Formatted cells to be recognised. Ground to a halt here, help...
You guys are awesome !! It works :)
This macro for "How to count by color and sum cells colored using conditional formatting" is a big help. Well done. I was wondering if there is a way to take the output that pops up in the Message Box and have that info put into a cell. For instance, the Count is what I am really looking for. Is there a way to have the Count displayed in a cell for the range selected instead of a popup window?
Hi Please help me with how i can use indirect formula in excel 2010 because when i m trying enter formula i am getting following error #REF!
I am entering formula indirect(select cells) but i am not getting when i am entering formula like this =indirect("Select") i am getting the outcome but not able to use for multiple cell and sheets need your help to get the indirect formula in excel 2010
This is a fantastic tip !
Thankyou so much :)
hi,
how to count the date between one week or two weeks.
for example in cell A1 is date. I want to count the date after one week( between 2 to 7weeks).what is the formula I can apply. I got the formula to count for today's date or overdue date countifs(a1:a10,"<=&today(),b1:b10,d15), but I cannot get the formula to count the date in between one week.
1/8/16
2/8/16
3/8/16
8/8/16
11/8/16
12/8/16
15/8/16
17/8/16
I followed the instructions verbatim for counting cells that contain a color and it didn't work. When I try to use the formula, Excel returns #NAME?
I did save the spreadsheet as a macro-enabled spreadsheet. I even went back into the VBA editor to make sure the code was still there and it was. (and I had copied the code verbatim by selecting it from this website and pasting it.)
Hi,
Your example is good one.
I am in the same problem, but what about merged cell. How do I count merged cell fill with specific color?
regardsd,
Santosh
Hi,
I'm using the color count for conditional formatting Excel 2010. How can you have the count be placed in a cell automatically after entering data instead of having to run the rule and only getting the result in a pop-up box?
Im using Excel 2013. What is the formula to count the cell color of specific information within the cell. EXAMPLE: I have APPLES(red), GRAPES(green) and STRAWBERRIES (red). I want to return the count of cells colored red that are STRAWBERRIES only, not all cells colored red.
Hi Carl, I'm needing the exact same info! I'd like to count by cell color, IF a specific text criteria is met (just like you said, Cell is RED and contains text "STRAWBERRIES"). I do a lot with data, and automating things like this would be a huge help. I've tried combining formulas and although they don't error out, I'm not achieving the response I need. Does anyone know if there is a solution?
I've seen the question I have a few times but there is never an answer associated. I run reports for my boss and some of them require totaling by sum and count for programs during the month based on conditional formatting. The formal works in that I can see the count and sum but, a) I need to include the numbers in my report and b) the count and sums are in separate boxes. Is there a way to populate the answer into a cell with the needed count or sum based on conditional formatting rather than a pop up box which doesn't work for my boss when I print the report.
Thank you, worked great
i can not able to see color and sum by color in an Excel worksheet.so i need help from you.please explain it brief
Hi,
Great stuff.. have learnt a lot for your website.
Need one help regarding sum of cells based on color formatting. The code provided is macro, how to convert this to a function so that I can use it as and when needed
Thanks,
Prasad
Thanks for the great article! I am working on a duel scoring system where each has a numerical score (1-5) and a traffic light score (by filling in the cell colour with red, Amber or green) the two scores are not related. Is it possible to modify the code so that it calculates the average score and fills in the cell colour according to the agreement of the traffic light score (all green = green, all red = red, anything else = Amber)?
To count cells by colour on the same worksheet, Microsoft recommends a macro based on just 10 lines of code, compared with you umpteen lines. Is there a special reason to your code?
I just noticed that the MS code just does a count, whereas your code sums as well. Apologies for my misunderstanding.