How to count and sum cells based on background color in Excel?

Supposing you have a range of cells with different background colors, such as red, green, blue and so on, but now you need to count how many cells in that range have a certain background color and sum the colored cells with the same certain color.

In Excel, there is no direct formula to calculate Sum and Count of color cells, here I will introduce you some ways to solve this problem.

Count and sum colored cells with User Defined Function

Count/Sum and do other calculations of colored cells with Kutools for Excel

Kutools for Excel: add 120 new features in Excel. Save one hour every day.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.

arrow blue right bubble Count and sum colored cells with User Defined Function

Hot
Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

The following code can help you count and sum the cells with a certain background color, please do as this:

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA: Count and sum cells based on backgroud color.

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

3. Then save the code, and apply the following formula:

Count the colored cells: =colorfunction(A,B:C,FALSE)

Sum the colored cells: =colorfunction(A,B:C,TRUE)

A: is the cell with the particular background color you want to calculate the count and sum.

B:C: is the cell range where you want to calculate the count and sum.

4. Take the following screenshot for example, enter the formula =colorfunction(A1,A1:D7,FALSE) to count the yellow cells. And use the formula =colorfunction(A1,A1:D7,TRUE) to sum the yellow cells. See screenshot:

doc-count-colored-cells1

5. If you want to count and sum other colored cells, please repeat the step 4. Then you will get the following results:

doc-count-colored-cells2


arrow blue right bubble Count/Sum and do other calculations of colored cells with Kutools for Excel

With the above User Defined Function, you need to enter the formula one by one, if there are lots of different colors, this method will be tedious and time-consuming. But if you have Kutools for Excel’s Count by Color utility, you can quickly generate a report of the colored cells. You not only can count and sum the colored cells, but also can get the average, max and min values of the colored range.

Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 30 days.Get it Now.

If you have installed Kutools for Excel, please do as following steps:

1. Select the range that you want to use.

2. Click Enterprise > Count by Color, see screenshot:

doc-count-colored-cells3

3. And in the Count by Color dialog box, all of the colors have been counted.

doc-count-colored-cells4

4. And then click Generate report button, you will get a new workbook with the statistics. See screenshot:

doc-count-colored-cells5

If you want to know more about this feature, please click Count by Color.


Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.

screen shot

btn read more     btn download     btn purchase

Comments  

+21#Gowrisankar2013-10-17 06:35
Very useful tool , Thank you verymuch
Reply | Reply with quote | Quote
+14#Gowrisankar2014-02-07 06:33
After inserting the function , when we change the value the sum not automatically udpated , any suggestion
Reply | Reply with quote | Quote
0#HR2014-04-30 11:01
Very useful. Thank you
Reply | Reply with quote | Quote
+1#Petr2013-10-17 12:51
Very good, thanks a lot !!!
Reply | Reply with quote | Quote
0#imamoglu2013-10-26 19:58
Very useful article, thanks a lot
Reply | Reply with quote | Quote
+2#Laxmannarayanan2013-10-30 06:25
Wonderful!!! Thank you so much!!!
Reply | Reply with quote | Quote
+4#Shankee2013-10-31 13:07
Amazing,thanks a lot
Reply | Reply with quote | Quote
+8#David Coe2013-11-06 15:33
This is great tool, thanks for sharing! I do have one question: I noticed that the macro formula does not update itself when you change a cell color from one color to another. Is there a way to get it to automatically update when cell colors change? If I double click on the cell with the addition formula and press enter, it updates, but I have a sheet with many of these cells and don't want to have to manually update it each time.
Reply | Reply with quote | Quote
0#skyyang2013-11-07 02:00
Thank you for your reply, I’m sorry, under the current situation, the VBA code can’t solve the question that you pointed out, you need to update them manually.
Reply | Reply with quote | Quote
+1#Happiqiu2013-12-27 06:44
Hi, i have tried. and the worksheet just needs an "Enter" No matter which cell. So, after coloring your cell, just type a memo on the colored cells and "enter"
Reply | Reply with quote | Quote
+8#Louise2014-02-07 21:57
try CTL+ALT+f9 it will update all cells with addition formulas at once. Not quite automatic but at least better than having to click on each cell with addition formula individually.
Reply | Reply with quote | Quote
+4#Sankar Narayanan2013-11-08 14:52
Hi, getting error. complie error, ambiguous error is coming.
Reply | Reply with quote | Quote
+1#RAMON2013-11-10 13:37
I applied the formula but the cell said NAMES? what I need to fix
Reply | Reply with quote | Quote
+4#skyyang2013-11-12 01:37
Hello RAMON, maybe you didn’t copy the above code into the Module. You must copy the code into the Module first and save it, then apply the formulas. You can try it. If it doesn’t help you, please let me know.
Reply | Reply with quote | Quote
+5#Alexandra2013-11-12 11:46
Hello, It worked the first time, but now, when I have to add a cell to the sum... I tried introducing again the code, saving, writing the formula again... it says "name?"
Reply | Reply with quote | Quote
+1#Daria2014-02-20 01:10
I'm having the same problem -- worked beautifully at first, but getting the NAME error after adding another cell. Have tried to delete module and add again, re-enter the function...no joy.
Reply | Reply with quote | Quote
0#Gary_99912014-04-24 14:59
You might need to enable Marcos again. Should flash up at the top of the screen depending on version of excel. :)
Reply | Reply with quote | Quote
+1#AvGravy2013-11-12 19:22
Thank you so much! This is genius, thanks for the help!
Reply | Reply with quote | Quote
0#aliinmegeve2013-11-13 15:58
Thank you SO much, i have been looking all day for a successful outcome and we now have one :)
Reply | Reply with quote | Quote
0#Kertis2013-11-14 11:27
Thanks. It worked for me as described. However, after I reopened the file next time all cells with this formula were showing error. I had to re-copy the coding again. Is there something I am missing? I will need to share the file with others and they wont be able to "fix" the problem.
Thanks, Ker.
Reply | Reply with quote | Quote
+1#Happiqiu2013-12-27 08:56
You need to save it as excel Macro workbook
Reply | Reply with quote | Quote
0#libin2013-11-14 15:35
well done..but..how it works in conditional formatting cell colors ???
Reply | Reply with quote | Quote
0#Ceza2013-11-18 15:02
This is awesome, thanks!
Reply | Reply with quote | Quote
+2#chaminda2013-11-21 06:53
it worked well done!!!
Reply | Reply with quote | Quote
+1#Ali2013-11-21 07:29
dear sir ,
in fact it is wonderful code but I faced a problem that it will continue calculate the cell even when we change the cell color
Reply | Reply with quote | Quote
+1#Anith2013-11-29 08:42
Thanks Sir,
This is a wonderful work and helps me a lot.
Reply | Reply with quote | Quote
+2#James B2013-12-04 08:52
I have an Excel file with many coloured cells. Is there any way to convert these colours to data? So a red cell will have ''red'' in it as data, a blue coloured cell will have ''blue'' in it and so on? James
Reply | Reply with quote | Quote
0#sri rangavalli2013-12-05 10:08
Timely and apt article like hitting the target which am looking for:) thanks
Reply | Reply with quote | Quote
+2#Ian2013-12-06 14:30
I can't seem to get this to work. Does it only work for cells that have been coloured manually? I need to count cells that have been coloured via Conditional Formatting, but it's not playing ball at the moment.
Reply | Reply with quote | Quote
+2#Ryan2013-12-13 19:14
I am having the same issue as Ian, I am trying to sum numbers based on the color rules set by Conditional Formatting but this doesnt seem to catch that.

Any Suggestions?
Reply | Reply with quote | Quote
+3#Admin_jay2014-01-08 03:07
We will try to enhance it in the upcoming versions. :-)
Reply | Reply with quote | Quote
+1#Jen2014-01-22 14:39
I threw a Powershell script that acts as a kind of work-around:

"
#setup Excel
$excelApp = New-Object -comobject Excel.Application
$excelApp.Quit()
$excelApp.Visible = $True
$workbook = $excelApp.Workbooks.Open("H:\Desktop\test.xlsx")#CHANGE THIS TO YOUR EXCEL FILE ADDRESS.
$worksheet = $workbook.Worksheets.Item("Sheet1")#CHANGE THIS IF YOU SHEET ISN'T CALLED "Sheet1"

#static variables
$row = 1
$column = 1#CHANGE THIS VARIABLE TO WHICHEVER COLUMN YOU'RE SEARCHING
$totalRow = $worksheet.UsedRange.Rows.Count

do{
$currentCell = $worksheet.cells.item($row, $column)

if($currentCell.text -eq "SEARCH_FOR_THIS") {
$worksheet.cells.item($row,$column).Interior.ColorIndex = 44#CHANGE THIS NUMBER TO CHANGE THE NEW-CELL COLOUR
"$row, $column = BLANK. Colouring"
}
$row++
$row

}
while($row -lt $totalRow)

"Script Complete."
"SAVING..."

$excelApp.Save
$excelApp.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelApp)

"

This script will search through your spreadsheet and if it finds a cell that contains "SEARCH_FOR_THIS" (you should probably change that part in the script) then it will turn that cell to whatever colour number is in the script (currently set to 44, a weird looking orange/tan colour). Once the script has finished it'll try and tell Excel that it wants to save, but Excel isn't a very trusting program so will ask for you to click on save. Once you've done that then it'll close down. Then, open the file back up and do all that business that listed on this page, making sure that the colour that my script it looking for it the same colour that the script listed on this page it looking for.

Hope this helps!
Reply | Reply with quote | Quote
-2#Andy2013-12-17 08:28
Legend! Thanks worked a charm
Reply | Reply with quote | Quote
0#sri rangavalli2013-12-18 07:35
hi Ian ,
i can suggest you an alternative: try using "SUM"
1.intially consolidate the count of different colored cells ,
eg :in my case i used conditional formatting to "if p-equal to green color) "(say (h4:h7)ranged cells) and then used to count using formula =COUNTIF(H10:H12,"P")in some cell (say in cell C4-giving you the count of "p:green coloured cells")
2.then similarly "if f-equal to red color) "(say (h4:h7)ranged cells) and then used to count using formula =ColorFunction(B5,H10:H12,TRUE)+COUNTIF(H10:H12,"F")in some cell (say in cell C5-giving you the count of "f:red coloured cells")
3. then use "SUM" : =SUM(C4:C5)where c4 = count of p:green coloured cells + c5 = f:red coloured cells

hope it helps you:)
Reply | Reply with quote | Quote
0#tayyab2013-12-23 06:56
Perfect!! n very helpful,
Reply | Reply with quote | Quote
-4#Md. Abdullah Sarker2013-12-24 10:19
Hi
This is Abdullah Sarker.I faced one problem in excel.Please suggest me.
Problem: How to count Or Sum Only color cell in excel.
Reply | Reply with quote | Quote
0#Rachael2013-12-26 07:02
Thanks a lot. It was really helpful. Now I have a very helpful and report-able expenses spreadsheet. Thanks again.
Reply | Reply with quote | Quote
0#Md. Abdullah Sarker2013-12-28 13:05
Thanks a lot. its working.
Reply | Reply with quote | Quote
0#Vimal Nakum2013-12-31 06:16
Very useful. It works!

Thanks a lot
Reply | Reply with quote | Quote
0#Tricky2014-01-07 10:24
Brilliant. Thank you.
BUT
can I ask if this can be taken a step further....
I want to look at a range of cells and only count the number of cells which contain certain text (e.g 2 letters)AND are filled with a certain colour.
Can you help?
Reply | Reply with quote | Quote
0#Matus2014-01-07 14:48
Hello, one question:
If I have in yelow cells for example dates and I want to count these yelow cells between 1.1.2014 and 1.2.2014?
How it should looks like?
Thank you...
Reply | Reply with quote | Quote
0#Admin_jay2014-01-08 03:30
Hello, you can try to use this feature to select those cells first. Please go to this link for more information about Select specific cells http://www.extendoffice.com/product/kutools-for-excel/excel-select-specific-cells-rows.html

And then please apply count by color.

:-)
Reply | Reply with quote | Quote
0#Kristi2014-01-08 12:09
Hello.
How can I save this code and apply the following formula?
Reply | Reply with quote | Quote
0#Mar2014-01-15 15:31
Hello, how do you get the median of specific colored cells with User Defined Functionand mode?
Reply | Reply with quote | Quote
0#vikram2014-01-23 10:53
fantastic. thank you so much for this informative post.
Reply | Reply with quote | Quote
0#Bing2014-01-23 23:53
Hi, I am using VBA to add up colored cells in a spreadsheet and all is working well. If I change the cells and update (+40) like this =20+30+40, then the total changes to #name? I have tried reloading the module, enter on the code, nothing fixes it. Basically any changes I make to the cells being counted, wrecks my sum of colored cells. Any help?
Reply | Reply with quote | Quote
0#cseinstein2014-01-24 18:00
This may be too far outside the purview of this post, so ignore if so. But, I wonder whether it is possible to use this function (the User Defined one) only on cells of a certain range (in my case, filled with values of 1-8, ignoring higher numbers). I can clarify with more information if requested.
Reply | Reply with quote | Quote
+1#pama2014-01-26 06:42
Thank you very much. Easy to set up and it works!
Reply | Reply with quote | Quote
0#Modeste2014-01-30 21:41
I am trying to add this macro to an exisiting excel macro enabled worksheet (.xlsm). However "colorfunction" is not recognized (pop up "Ambiguous Function" comes up)when I try to add it to a separate worksheet in the exisiting .xlsm file. It does work when I add it to a new file from scratch but not when I try to incorporate it in an exisiting .xlsm file. Is there something I'm missing? Please assist.
Reply | Reply with quote | Quote
0#Madhuka2014-02-03 06:01
Thanks and very useful !
Reply | Reply with quote | Quote
0#Brenda2014-02-03 16:40
Very Helpful. Thank you for sharing!!! :-)
Reply | Reply with quote | Quote
0#Josh2014-02-04 17:41
I am not sure if this question has already been asked, but how do you use the colorfunction when looking to find the sum of colored cells across many worksheets within the same Excel file? Thanks.
Reply | Reply with quote | Quote
0#Ahsan2014-02-14 17:50
Hi,
If I change the cell color using another formula and then ask to calculate sum up with the above formula then it doesn't work. Can you please help me resolving the problem?
Reply | Reply with quote | Quote
+1#Tomas2014-02-18 23:50
Thanks, a perfect hint! :-)
Reply | Reply with quote | Quote
+1#Ardhi2014-02-21 07:59
It works like charm !!! Thank you very much !!
Reply | Reply with quote | Quote
+1#Beefmitten2014-02-26 08:51
Brilliant, Exactly what I was looking for.
Reply | Reply with quote | Quote
+1#Tricky2014-02-26 09:53
I am still struggling to resolve my problem.
I need to count the number of cells within a range of cells which....
1. contain text which match the text in a particular cell,
AND
2. has a background colour that match another particular cell.
I need the counting to be automatic as I change the text or colour of the cells within the range of cells.
Can anyone lease help?
Reply | Reply with quote | Quote
+1#Swindle2014-02-27 15:05
Quick question, how do you count and sum cells from other worksheets? The summing functions don't work because they are not part of the colorfunction module. Is there a work around? Any help would be appreciated.
Reply | Reply with quote | Quote
+1#jeffshieldsdev2014-03-03 13:37
You might want to use the Color property instead of ColorIndex, as it'll be more precise. For instance, I just found a light red and light green colors both return a ColorIndex of 19, but their Color property returns 14474738 and 14545386, respectively.
Reply | Reply with quote | Quote
+1#Adele2014-04-03 11:50
Jeff I have changed the wording Colorindex to ColorProperty and now it returns #VALUE. Any ideas what I have done wrong?
Reply | Reply with quote | Quote
+1#Bobbyb2014-03-05 14:44
Thank you!!. Love this function!!!!
Reply | Reply with quote | Quote
-1#timbo2014-03-19 15:09
SOrry if this has been asked before but how do I do a countif in the colour of a value using HUtools or not...???????
Reply | Reply with quote | Quote
0#Leena2014-03-24 06:35
For some reason the formula seems to be considering some colors the same - for example when I enter a formula to count all the green cells, it counts both green and red cells, and vice versa. It only seems to be happening with some colors, while others work just fine.
Reply | Reply with quote | Quote
-1#shahid ashraf2014-04-03 02:05
thank man very help ful job, God bless u
Reply | Reply with quote | Quote
0#Trish2014-04-10 10:02
Thanks this is exactly what I needed.
Reply | Reply with quote | Quote
-1#safa2014-04-16 11:32
very nice tool, thanks from the bottom of my heart. May god bless
U
Reply | Reply with quote | Quote
0#Matt_Priebe2014-04-16 16:27
This worrked great on the first shot. How do I set it up so if you change a colour it automatically regenerates?
Reply | Reply with quote | Quote
0#Stephanie2014-04-28 14:27
Very clear instructions, thank you :)
Reply | Reply with quote | Quote
+1#tim2014-04-29 14:18
OK folks here is a very easyway I discovered that will allow you to count the number of cellls in a spreadsheet that are say RED. Rather than just set the cell background to red also place a red r in the cell and then format the cell background red and you will not see the red r but its there.
You then set a countif argument...=COUNTIF(H7:H11,"r") to capture the number of values r in cells h7 through h11.....very simple but works very very well.
Reply | Reply with quote | Quote
0#Appaswamy2014-04-30 11:29
itsVery good.... and useful
Reply | Reply with quote | Quote
0#Joe jamies2014-05-17 09:56
Very helpful indeed. Thumbs up! :lol:
Reply | Reply with quote | Quote
0#Caio Alve2014-05-25 16:14
Thank you very much.
Reply | Reply with quote | Quote
0#Brittany2014-06-18 18:57
I might've missed the instructions in the thread for this, but how do you get it calculate automatically ?
Reply | Reply with quote | Quote
0#Gabe2014-06-27 18:43
That works. Thanks a lot.
Reply | Reply with quote | Quote
0#rogsh2014-07-07 19:14
GREAT, two tomb, be invaluable aid
Reply | Reply with quote | Quote
0#Pete2014-07-10 23:48
Awesome, thanks so much for this VB code, works a charm.
Reply | Reply with quote | Quote
0#faizal2014-07-17 16:51
Excellent work.. Thanks :)
Reply | Reply with quote | Quote

Add comment


Security code
Refresh