## 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.

#### Count and sum colored cells with User Defined Function

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

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

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

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

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

### Kutools for Excel

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

+21#Gowrisankar2013-10-17 06:35
Very useful tool , Thank you verymuch
+14#Gowrisankar2014-02-07 06:33
After inserting the function , when we change the value the sum not automatically udpated , any suggestion
-1#HR2014-04-30 11:01
Very useful. Thank you
0#Petr2013-10-17 12:51
Very good, thanks a lot !!!
-1#imamoglu2013-10-26 19:58
Very useful article, thanks a lot
+1#Laxmannarayanan2013-10-30 06:25
Wonderful!!! Thank you so much!!!
+3#Shankee2013-10-31 13:07
Amazing,thanks a lot
+7#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.
-1#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.
0#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"
+7#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.
+3#Sankar Narayanan2013-11-08 14:52
Hi, getting error. complie error, ambiguous error is coming.
0#RAMON2013-11-10 13:37
I applied the formula but the cell said NAMES? what I need to fix
+3#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.
+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?"
0#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.
-1#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. :)
0#AvGravy2013-11-12 19:22
Thank you so much! This is genius, thanks for the help!
-1#aliinmegeve2013-11-13 15:58
Thank you SO much, i have been looking all day for a successful outcome and we now have one :)
-1#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.
0#Happiqiu2013-12-27 08:56
You need to save it as excel Macro workbook
-1#libin2013-11-14 15:35
well done..but..how it works in conditional formatting cell colors ???
-1#Ceza2013-11-18 15:02
This is awesome, thanks!
+1#chaminda2013-11-21 06:53
it worked well done!!!
0#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
0#Anith2013-11-29 08:42
Thanks Sir,
This is a wonderful work and helps me a lot.
+1#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
-1#sri rangavalli2013-12-05 10:08
Timely and apt article like hitting the target which am looking for:) thanks
+1#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.
+1#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?
We will try to enhance it in the upcoming versions.
0#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
\$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!
-3#Andy2013-12-17 08:28
Legend! Thanks worked a charm
-1#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:)
-1#tayyab2013-12-23 06:56
-6#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.
-1#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.
-1#Md. Abdullah Sarker2013-12-28 13:05
Thanks a lot. its working.
-1#Vimal Nakum2013-12-31 06:16
Very useful. It works!

Thanks a lot
-1#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?
-1#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...

And then please apply count by color.

-1#Kristi2014-01-08 12:09
Hello.
How can I save this code and apply the following formula?
-1#Mar2014-01-15 15:31
Hello, how do you get the median of specific colored cells with User Defined Functionand mode?
-1#vikram2014-01-23 10:53
fantastic. thank you so much for this informative post.
-1#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?
-1#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.
0#pama2014-01-26 06:42
Thank you very much. Easy to set up and it works!
-1#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.
Thanks and very useful !
-1#Brenda2014-02-03 16:40
Very Helpful. Thank you for sharing!!!
-1#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.
-1#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?
0#Tomas2014-02-18 23:50
Thanks, a perfect hint!
0#Ardhi2014-02-21 07:59
It works like charm !!! Thank you very much !!
0#Beefmitten2014-02-26 08:51
Brilliant, Exactly what I was looking for.
0#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?
0#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.
0#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.
Jeff I have changed the wording Colorindex to ColorProperty and now it returns #VALUE. Any ideas what I have done wrong?
0#Bobbyb2014-03-05 14:44
Thank you!!. Love this function!!!!
-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...???????
-1#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.
-2#shahid ashraf2014-04-03 02:05
thank man very help ful job, God bless u
-1#Trish2014-04-10 10:02
Thanks this is exactly what I needed.
-2#safa2014-04-16 11:32
very nice tool, thanks from the bottom of my heart. May god bless
U
-1#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?
-1#Stephanie2014-04-28 14:27
Very clear instructions, thank you :)
-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.
-1#Appaswamy2014-04-30 11:29
itsVery good.... and useful
-1#Joe jamies2014-05-17 09:56
-1#Caio Alve2014-05-25 16:14
Thank you very much.
-1#Brittany2014-06-18 18:57
I might've missed the instructions in the thread for this, but how do you get it calculate automatically ?
-1#Gabe2014-06-27 18:43
That works. Thanks a lot.
-1#rogsh2014-07-07 19:14
GREAT, two tomb, be invaluable aid
-1#Pete2014-07-10 23:48
Awesome, thanks so much for this VB code, works a charm.
-1#faizal2014-07-17 16:51
Excellent work.. Thanks :)
-1#Tyron2014-07-23 06:19
Awesome! Works like a charm.

1. A way to update when cell colors change - from what I see here it looks like I will need to have an additional macro button to update the calculation.

2. A way to calculate cells whose color changes based on conditional formatting.

If it had both these two elements it would be a golden piece of code.
0#Benjiman2014-07-30 14:08
I second Tyron's request. I need this colorfuntion to update automatically when the source data is changed.
0#Dileepa2014-08-06 10:23
Works perfectly.. Thanks a lot
0#Manish2014-08-12 09:39
Great Tool. However, can any one help me to exclude the blank cell from the count formula.
0#Hassan2014-08-13 09:34
Man i fucken love you. God bless you
0#Jamie2014-08-16 09:17
Keeps saying ambiguous name detected:ColorFunction

Sorry very inexperienced at excel

But thanks for any help
0#Cindy2014-08-22 16:40
Yes - I'm having same problem. I've saved as macro enabled, exited and came back in and enabled macros. I'm trying to incorporate macro into existing worksheet and keep getting "ambiguous" error. Doesn't seem to work for existing spreadsheets.