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


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


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. Read More   Free Download 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 by color 3

3. And in the Count by Color dialog box, all of the colors have been counted. You can filter the color cells based on your needs. For example, you want to count the cells whose background is generated by standard formatting (or conditional formatting), you can filter as below:

(1) Click the Color method box and then select Standard formatting (or Conditional formatting) from drop down list;

(2) Click the Count type box and select Background from drop down list.

doc count by color 4

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

doc count by color 5

If you want to know more about this feature, please click Count by Color       Free Download Kutools for Excel Now


Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Comments  

Permalink +30 Gowrisankar
Very useful tool , Thank you verymuch
2013-10-17 06:35 Reply Reply with quote Quote
Permalink +28 Gowrisankar
After inserting the function , when we change the value the sum not automatically udpated , any suggestion
2014-02-07 06:33 Reply Reply with quote Quote
Permalink +2 WestwoodMike
Alt-Ctrl-F9 will re-calculate
2015-11-16 14:51 Reply Reply with quote Quote
Permalink +1 12345678998765432154
Add a second line directly below the first that says Application.vol itile it makes it recalculate after something is updated
2016-01-09 07:02 Reply Reply with quote Quote
Permalink 0 Jarod
I tried to put Application.Vol atile unde the first line and nothing hapens :(
2016-02-19 17:59 Reply Reply with quote Quote
Permalink 0 abrugg
Quoting 12345678998765432154:
Add a second line directly below the first that says Application.vol itile it makes it recalculate after something is updated


Application.Vol atile is the correct function not Application.vol itile
2016-06-24 01:57 Reply Reply with quote Quote
Permalink 0 Jo
I don't see any line that says application.volatile.
Where do I find it?
2016-08-17 11:08 Reply Reply with quote Quote
Permalink 0 Jacqui
I double clicked the cell with the ColorFunction formula, and then pressed Enter. It updated.
2016-12-28 20:05 Reply Reply with quote Quote
Permalink 0 HR
Very useful. Thank you
2014-04-30 11:01 Reply Reply with quote Quote
Permalink 0 Aniko
I also tried the formula.
Works in the first row and does not with second and third.

????
2016-03-30 13:50 Reply Reply with quote Quote
Permalink 0 Mark
I agree with Jarod.

The first two lines of my function says:

Function ColorFunction(r Color As Range, rRange As Range, Optional SUM As Boolean)
Application.Volatile

The problem is that only a Alt-Ctrl-F9 will re-calculate. We're looking for a more automated solution. Ideas?
2017-01-13 20:05 Reply Reply with quote Quote
Permalink 0 Mark
Try it like this:

1. be certain you have the function saved as described

2. permit me to use an example
* I have hundreds of rows of data
* I use columns A to AB with more data
* whenever I have an issue with my data, I highlight it in yellow
* I use this cool formula to 'count' the number of highlights on each row

3. How to count the number of my highlights per row
a. figure the range of cellsthat could have highlights that you want to count (or sum)
* for me, I want to COUNT the quantity of my flagged highlights on each row (my range)
b. pick a cell where you will report the count (or sum)
* for me, I placed it on the far right of my data...in column AE
c. insert the following formula in the cell you chose in item b (above)
=colorfunction( AE3,A3:AB3,FALS E)
* for me, I placed this formula in cell AE3 (the end of my row) AND AND I highlighted the same formula cell Yellow
d. I then copied this formula down for all my data rows

4. I observed
a. no counts were made. (bad)

5 KEY ISSUE HERE:
a. I pressed CTL+ ALT + F9
. PRESTO !!! it works.!

hope this banter is of some help.

Cheers




do this:

A B C D
1
2
3
4
2017-01-13 20:27 Reply Reply with quote Quote
Permalink +1 Petr
Very good, thanks a lot !!!
2013-10-17 12:51 Reply Reply with quote Quote
Permalink -1 imamoglu
Very useful article, thanks a lot
2013-10-26 19:58 Reply Reply with quote Quote
Permalink -1 Laxmannarayanan
Wonderful!!! Thank you so much!!!
2013-10-30 06:25 Reply Reply with quote Quote
Permalink +2 Shankee
Amazing,thanks a lot
2013-10-31 13:07 Reply Reply with quote Quote
Permalink +14 David Coe
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.
2013-11-06 15:33 Reply Reply with quote Quote
Permalink -4 skyyang
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.
2013-11-07 02:00 Reply Reply with quote Quote
Permalink 0 Jako
I have also found that using the format painter to update the cell color will cause the formulas to work as expected. Still not perfect.
2016-07-12 17:14 Reply Reply with quote Quote
Permalink +1 Happiqiu
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"
2013-12-27 06:44 Reply Reply with quote Quote
Permalink +15 Louise
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.
2014-02-07 21:57 Reply Reply with quote Quote
Permalink +1 ollie
this did nothing for me :(
2015-11-29 15:18 Reply Reply with quote Quote
Permalink +2 Sankar Narayanan
Hi, getting error. complie error, ambiguous error is coming.
2013-11-08 14:52 Reply Reply with quote Quote
Permalink -1 RAMON
I applied the formula but the cell said NAMES? what I need to fix
2013-11-10 13:37 Reply Reply with quote Quote
Permalink +4 skyyang
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.
2013-11-12 01:37 Reply Reply with quote Quote
Permalink +4 Alexandra
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?"
2013-11-12 11:46 Reply Reply with quote Quote
Permalink -1 Daria
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.
2014-02-20 01:10 Reply Reply with quote Quote
Permalink -2 Gary_9991
You might need to enable Marcos again. Should flash up at the top of the screen depending on version of excel. :)
2014-04-24 14:59 Reply Reply with quote Quote
Permalink 0 Mike F
Skyyang,

If I email you a document can you help me with it?

Mike
2016-10-18 14:32 Reply Reply with quote Quote
Permalink 0 AvGravy
Thank you so much! This is genius, thanks for the help!
2013-11-12 19:22 Reply Reply with quote Quote
Permalink -1 aliinmegeve
Thank you SO much, i have been looking all day for a successful outcome and we now have one :)
2013-11-13 15:58 Reply Reply with quote Quote
Permalink -3 Kertis
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.
2013-11-14 11:27 Reply Reply with quote Quote
Permalink -1 Happiqiu
You need to save it as excel Macro workbook
2013-12-27 08:56 Reply Reply with quote Quote
Permalink -1 libin
well done..but..how it works in conditional formatting cell colors ???
2013-11-14 15:35 Reply Reply with quote Quote
Permalink -1 Ceza
This is awesome, thanks!
2013-11-18 15:02 Reply Reply with quote Quote
Permalink +1 chaminda
it worked well done!!!
2013-11-21 06:53 Reply Reply with quote Quote
Permalink -2 Ali
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
2013-11-21 07:29 Reply Reply with quote Quote
Permalink 0 Anith
Thanks Sir,
This is a wonderful work and helps me a lot.
2013-11-29 08:42 Reply Reply with quote Quote
Permalink -1 James B
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
2013-12-04 08:52 Reply Reply with quote Quote
Permalink -1 sri rangavalli
Timely and apt article like hitting the target which am looking for:) thanks
2013-12-05 10:08 Reply Reply with quote Quote
Permalink 0 Ian
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.
2013-12-06 14:30 Reply Reply with quote Quote
Permalink +1 Ryan
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?
2013-12-13 19:14 Reply Reply with quote Quote
Permalink +3 Admin_jay
We will try to enhance it in the upcoming versions. :-)
2014-01-08 03:07 Reply Reply with quote Quote
Permalink -1 Jen
I threw a Powershell script that acts as a kind of work-around:

"
#setup Excel
$excelApp = New-Object -comobject Excel.Applicati on
$excelApp.Quit( )
$excelApp.Visib le = $True
$workbook = $excelApp.Workb ooks.Open("H:\D esktop\test.xls x")#CHANGE THIS TO YOUR EXCEL FILE ADDRESS.
$worksheet = $workbook.Works heets.Item("She et1")#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.Used Range.Rows.Coun t

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

if($currentCell .text -eq "SEARCH_FOR_THI S") {
$worksheet.cell s.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_THI S" (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!
2014-01-22 14:39 Reply Reply with quote Quote
Permalink -3 Andy
Legend! Thanks worked a charm
2013-12-17 08:28 Reply Reply with quote Quote
Permalink -1 sri rangavalli
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:H1 2,"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:H 12,"F")in some cell (say in cell C5-giving you the count of "f:red coloured cells")
3. then use "SUM" : =SUM(C4:C5)wher e c4 = count of p:green coloured cells + c5 = f:red coloured cells

hope it helps you:)
2013-12-18 07:35 Reply Reply with quote Quote
Permalink -1 tayyab
Perfect!! n very helpful,
2013-12-23 06:56 Reply Reply with quote Quote
Permalink -8 Md. Abdullah Sarker
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.
2013-12-24 10:19 Reply Reply with quote Quote
Permalink 0 Rachael
Thanks a lot. It was really helpful. Now I have a very helpful and report-able expenses spreadsheet. Thanks again.
2013-12-26 07:02 Reply Reply with quote Quote
Permalink -2 Md. Abdullah Sarker
Thanks a lot. its working.
2013-12-28 13:05 Reply Reply with quote Quote
Permalink -1 Vimal Nakum
Very useful. It works!

Thanks a lot
2013-12-31 06:16 Reply Reply with quote Quote
Permalink 0 Tricky
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?
2014-01-07 10:24 Reply Reply with quote Quote
Permalink -1 Matus
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...
2014-01-07 14:48 Reply Reply with quote Quote
Permalink -1 Admin_jay
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.

:-)
2014-01-08 03:30 Reply Reply with quote Quote
Permalink -3 Kristi
Hello.
How can I save this code and apply the following formula?
2014-01-08 12:09 Reply Reply with quote Quote
Permalink -1 Mar
Hello, how do you get the median of specific colored cells with User Defined Functionand mode?
2014-01-15 15:31 Reply Reply with quote Quote
Permalink -1 vikram
fantastic. thank you so much for this informative post.
2014-01-23 10:53 Reply Reply with quote Quote
Permalink -1 Bing
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?
2014-01-23 23:53 Reply Reply with quote Quote
Permalink 0 Ashley
I have had the same issue. The VBA works perfectly a few times and then it stopped after I closed out, restarted, and then opened. I tried deleting and reloading the macro but it still won't work.
2016-12-12 15:51 Reply Reply with quote Quote
Permalink -1 cseinstein
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.
2014-01-24 18:00 Reply Reply with quote Quote
Permalink 0 pama
Thank you very much. Easy to set up and it works!
2014-01-26 06:42 Reply Reply with quote Quote
Permalink -1 Modeste
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.
2014-01-30 21:41 Reply Reply with quote Quote
Permalink -1 Madhuka
Thanks and very useful !
2014-02-03 06:01 Reply Reply with quote Quote
Permalink -1 Brenda
Very Helpful. Thank you for sharing!!! :-)
2014-02-03 16:40 Reply Reply with quote Quote
Permalink -1 Josh
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.
2014-02-04 17:41 Reply Reply with quote Quote
Permalink -1 Ahsan
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?
2014-02-14 17:50 Reply Reply with quote Quote
Permalink 0 Tomas
Thanks, a perfect hint! :-)
2014-02-18 23:50 Reply Reply with quote Quote
Permalink 0 Ardhi
It works like charm !!! Thank you very much !!
2014-02-21 07:59 Reply Reply with quote Quote
Permalink 0 Beefmitten
Brilliant, Exactly what I was looking for.
2014-02-26 08:51 Reply Reply with quote Quote
Permalink 0 Tricky
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?
2014-02-26 09:53 Reply Reply with quote Quote
Permalink 0 Swindle
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.
2014-02-27 15:05 Reply Reply with quote Quote
Permalink 0 jeffshieldsdev
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.
2014-03-03 13:37 Reply Reply with quote Quote
Permalink 0 Adele
Jeff I have changed the wording Colorindex to ColorProperty and now it returns #VALUE. Any ideas what I have done wrong?
2014-04-03 11:50 Reply Reply with quote Quote
Permalink 0 Bobbyb
Thank you!!. Love this function!!!!
2014-03-05 14:44 Reply Reply with quote Quote
Permalink -1 timbo
SOrry if this has been asked before but how do I do a countif in the colour of a value using HUtools or not...???????
2014-03-19 15:09 Reply Reply with quote Quote
Permalink -1 Leena
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.
2014-03-24 06:35 Reply Reply with quote Quote
Permalink -1 shahid ashraf
thank man very help ful job, God bless u
2014-04-03 02:05 Reply Reply with quote Quote
Permalink 0 Trish
Thanks this is exactly what I needed.
2014-04-10 10:02 Reply Reply with quote Quote
Permalink -1 safa
very nice tool, thanks from the bottom of my heart. May god bless
U
2014-04-16 11:32 Reply Reply with quote Quote
Permalink 0 Matt_Priebe
This worrked great on the first shot. How do I set it up so if you change a colour it automatically regenerates?
2014-04-16 16:27 Reply Reply with quote Quote
Permalink 0 Stephanie
Very clear instructions, thank you :)
2014-04-28 14:27 Reply Reply with quote Quote
Permalink -1 tim
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...=COU NTIF(H7:H11,"r" ) to capture the number of values r in cells h7 through h11.....very simple but works very very well.
2014-04-29 14:18 Reply Reply with quote Quote
Permalink 0 Appaswamy
itsVery good.... and useful
2014-04-30 11:29 Reply Reply with quote Quote
Permalink 0 Joe jamies
Very helpful indeed. Thumbs up! :lol:
2014-05-17 09:56 Reply Reply with quote Quote
Permalink 0 Caio Alve
Thank you very much.
2014-05-25 16:14 Reply Reply with quote Quote
Permalink 0 Brittany
I might've missed the instructions in the thread for this, but how do you get it calculate automatically ?
2014-06-18 18:57 Reply Reply with quote Quote
Permalink 0 Gabe
That works. Thanks a lot.
2014-06-27 18:43 Reply Reply with quote Quote
Permalink 0 rogsh
GREAT, two tomb, be invaluable aid
2014-07-07 19:14 Reply Reply with quote Quote
Permalink 0 Pete
Awesome, thanks so much for this VB code, works a charm.
2014-07-10 23:48 Reply Reply with quote Quote
Permalink +1 faizal
Excellent work.. Thanks :)
2014-07-17 16:51 Reply Reply with quote Quote
Permalink -2 Tyron
Awesome! Works like a charm.

Two items to add:

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.
2014-07-23 06:19 Reply Reply with quote Quote
Permalink 0 Benjiman
I second Tyron's request. I need this colorfuntion to update automatically when the source data is changed.
2014-07-30 14:08 Reply Reply with quote Quote
Permalink 0 Dileepa
Works perfectly.. Thanks a lot
2014-08-06 10:23 Reply Reply with quote Quote
Permalink 0 Manish
Great Tool. However, can any one help me to exclude the blank cell from the count formula.
2014-08-12 09:39 Reply Reply with quote Quote
Permalink 0 Hassan
Man i fucken love you. God bless you
2014-08-13 09:34 Reply Reply with quote Quote
Permalink 0 Jamie
Keeps saying ambiguous name detected:ColorFunction

Sorry very inexperienced at excel

But thanks for any help
2014-08-16 09:17 Reply Reply with quote Quote
Permalink 0 Cindy
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.
2014-08-22 16:40 Reply Reply with quote Quote
Permalink 0 Ahmad Raza
Excellent function. It works for me. Thanks a lot.
2014-09-05 06:29 Reply Reply with quote Quote
Permalink 0 Dean
Excellent function, worked first time. Thank You.
2014-09-06 02:26 Reply Reply with quote Quote
Permalink 0 Hilary
Still didn't work for me :(It worked great in the beginning when I first apply the codes, but it would not calculate again when I reopen the file. I've already saved it in "Excel Macro-Enabled Workbook" but still wouldn't work. :( Is there anything I'm doing wrong?
2014-09-10 17:44 Reply Reply with quote Quote
Permalink 0 Melanie
Thank you so much, this is awesome!
2014-09-10 20:55 Reply Reply with quote Quote
Permalink 0 Amar
Thanks.. this formula tutorial was very helpful !!
2014-09-15 11:57 Reply Reply with quote Quote
Permalink 0 Fany
Hi, thanks for this post. I tried to apply the formula and gives me just 1 as a result... don't know what Im doing wrong
2014-09-16 18:05 Reply Reply with quote Quote
Permalink 0 nikhil
i have used conditional formatting to colour certain cells based on a set of rules. however i do not know how it colours the cell as the background colour of the cell is still white even though the cell is the required colour.

I was hoping you would be able to adapt the function for cells coloured using conditional formatting
2014-09-20 12:30 Reply Reply with quote Quote
Permalink 0 traeden
Hi,

copy and pasted the formula directly. When I try and use function I get a dialogue box that says "compile error: Expected: end of statement" it also opens VBA and highlights the word "Dim" "Optional SUM As Boolen)Dim" in the code you pasted.

Thanks,
2014-09-24 15:03 Reply Reply with quote Quote
Permalink 0 Kelvin
Hi,

Thank you for sharing the codes on count by colours in such a clear and simple way.
2014-10-02 10:29 Reply Reply with quote Quote
Permalink 0 Manivannan
Awesome... It is really help and very easy to use... Thank you!
2014-10-06 10:47 Reply Reply with quote Quote
Permalink 0 Nik
Wonderful bit of code - thanks so much!
2014-10-06 20:02 Reply Reply with quote Quote
Permalink 0 Srinu
Hi ,i have applied conditional formatting for color coding ,now i need to count & sum the colored cells ,plz help
2014-10-08 05:43 Reply Reply with quote Quote
Permalink 0 Sree
Thanks a ton. This is awesome.
2014-10-09 20:39 Reply Reply with quote Quote
Permalink 0 Tiny
how do you calculate average using this?
2014-10-10 17:26 Reply Reply with quote Quote
Permalink 0 Thirumalraj
Very Helpful...thank s a lott :)
2014-10-15 11:50 Reply Reply with quote Quote
Permalink 0 Bmetz
Nice, useful function! The colorfunction works as expected on a large worksheet that enables not having to manually calculate entries after color format changes. :-)
2014-10-17 19:47 Reply Reply with quote Quote
Permalink 0 FRomero
So... I want to count all cells colored pink (Q3) and with a criteria of >50...

Here is the formula...
=COUNTIFS(CA!A2:A10000,colorfunction(Q3,CA!B2:B10000,FALSE),CA!B2:B10000,">50")

It keeps returning "0" which is wrong, what can to get the real Value?
2014-10-25 21:27 Reply Reply with quote Quote
Permalink 0 Ty
Quoting FRomero:
So... I want to count all cells colored pink (Q3) and with a criteria of >50...

Here is the formula...
=COUNTIFS(CA!A2:A10000,colorfunction(Q3,CA!B2:B10000,FALSE),CA!B2:B10000,">50")

It keeps returning "0" which is wrong, what can to get the real Value?


Hey FRomero,

Well first thing that I see is that you are combining the colorfunction within a CountIF function. Have you established that you are able to attain the desired result by using helper cells. Thus, one cell to determine the value of the Colorfunction and then a second cell to determine the remainder of the CountIF?
I personally have only used a range including up to 1,000 cells. Is the Colorfunction capabale of a range so large? Just some questions that might help you acheive the answer.
later

Ty
2014-10-27 07:36 Reply Reply with quote Quote
Permalink 0 Dan
Quoting Ty:
Quoting FRomero:
So... I want to count all cells colored pink (Q3) and with a criteria of >50...

Here is the formula...
=COUNTIFS(CA!A2:A10000,colorfunction(Q3,CA!B2:B10000,FALSE),CA!B2:B10000,">50")

It keeps returning "0" which is wrong, what can to get the real Value?


Hey FRomero,

Well first thing that I see is that you are combining the colorfunction within a CountIF function. Have you established that you are able to attain the desired result by using helper cells. Thus, one cell to determine the value of the Colorfunction and then a second cell to determine the remainder of the CountIF?
I personally have only used a range including up to 1,000 cells. Is the Colorfunction capabale of a range so large? Just some questions that might help you acheive the answer.
later

Ty


Did you ever get an answer that makes this work? I need to do the same thing. I've been trying to modify the colorfunction code itself, but i'm not programmer so I can't get it to work.
2016-05-17 21:05 Reply Reply with quote Quote
Permalink 0 Ashish
It's perfect. But I was expecting some improvement. Instead of passing cell number to pick up the base color, can I pass the color itself in the formula?

Quote:
Example: instead of =colorfunction(G31,C3:G27,FALSE), can I do =colorfunction(FF00FF,C3:G27,FALSE)
Otherwise, it's amazing. Thanks!!
2014-10-29 06:11 Reply Reply with quote Quote
Permalink 0 Waqas
Really helpful. Thank you.
2014-11-26 10:08 Reply Reply with quote Quote
Permalink +1 Anon
I had issues with this discovering different shades of colours. It looks like different shades can have the same colour index which then gives incorrect counts.

I'm using Interior.Color instead of Interior.ColorI ndex which gives the correct result.
2014-12-01 11:22 Reply Reply with quote Quote
Permalink 0 Megan
Thank you, this saved the day for me!
2016-02-12 18:32 Reply Reply with quote Quote
Permalink 0 WAQAS
Remarkable
Very helpful
continue your good work
2014-12-02 11:53 Reply Reply with quote Quote
Permalink 0 Mike N
I entered the function and it works great! Only issues is the doc is very slow when using basic functions like a filter. it's only 27kb. Could you advise a solution to this? Thanks!!
2014-12-05 00:40 Reply Reply with quote Quote
Permalink +1 JoeG
Amazing thank you!

If there was an addition for the formulas to update automatically when the source data changes that would be totally amazeballs, but otherwise it's pretty cool :))
2015-01-08 23:08 Reply Reply with quote Quote
Permalink 0 Rem
Hey, quite usefull the count fucntion but now I'd need to add a data criteria.
I would like to count a cell colored only if a data is in it. So if not data in a colored cell, do not take it in account. How can it works?
Thanks
2015-02-06 23:33 Reply Reply with quote Quote
Permalink 0 Vim
For some reason the UDF returns #NAME when I enter the formula. Any ideas on why that could be be happening? I inserted the module and pasted the code above. Then saved and went to file-> Close and return to Microsoft Excel.
2015-02-13 21:04 Reply Reply with quote Quote
Permalink 0 sokly
how to run code in this page
2015-04-28 05:35 Reply Reply with quote Quote
Permalink 0 hoori
Thanks. It works the best and it saved me :D
2015-05-08 09:26 Reply Reply with quote Quote
Permalink 0 Martin Pryce
Thanks for this - extremely helpful and straightforward to follow.
2015-05-20 14:15 Reply Reply with quote Quote
Permalink 0 Sankara
hi i want to add cells say A1 Sankar 130,A2 Vibav 230 I have higlighted the text as blue and green,when i give the above function its showing !value.how can we add only numbers highlighted by neglecting it
2015-06-17 15:04 Reply Reply with quote Quote
Permalink 0 Luke Newham
Hi There,

Many thanks for this - I am reporting on cell colours and the above has been useful. However, I am facing a problem whereby when I automatically colour cells using conditional formatting, the above code is not recognising those cells as being coloured. Is this something that can be catered for?
2015-07-16 10:13 Reply Reply with quote Quote
Permalink +1 Cris
Extremely useful. Thanks for sharing.
2015-07-25 15:15 Reply Reply with quote Quote
Permalink 0 Bayar Faisal
Very useful article, thank you a lot.
Regarding the VBA code, what if I have merged cells?
I have 62 total, but some of the are merged, my total colored are 27, but I want them to be six as they are merged.
2015-07-29 10:56 Reply Reply with quote Quote
Permalink 0 Nitin Verma
Dear Friends,
Pls do me favour, if u have Kutool activation key or activator..pls send me on this mail id .
2015-07-31 06:11 Reply Reply with quote Quote
Permalink 0 Arpad Kegl
:lol:
very usefull
thanks a lot
2015-08-27 11:38 Reply Reply with quote Quote
Permalink 0 Moataz
Hi,
it's great, but why it's not working automatically?
is it possible to make it working automatically?
i hop that, thanks.
2015-09-07 11:23 Reply Reply with quote Quote
Permalink 0 Tracy
This was very helpful. Thank you. :-)
2015-09-09 16:36 Reply Reply with quote Quote
Permalink 0 Harpreet Singh
Its Done, But when we clear the color cell number of counting of colors can't up or down
2015-09-14 08:15 Reply Reply with quote Quote
Permalink 0 Harpreet Singh
its done, But when we clear the cell from color the number of counting did not down
2015-09-14 08:17 Reply Reply with quote Quote
Permalink 0 Abdul Rashid
Thanks Sir,
This is a wonderful work and helps me a lot.
2015-09-26 14:01 Reply Reply with quote Quote
Permalink 0 Muhammad Faizan
Thankyou. very helpful information
2015-09-30 13:34 Reply Reply with quote Quote
Permalink 0 Tim Nevins
Count by color function worked perfectly with just a copy/paste. I've been fooling with this for a couple of hours. Wish I'd found this site first.
2015-10-14 18:59 Reply Reply with quote Quote
Permalink +1 Usman
Thanks a million! The color function works perfectly!
2015-10-31 07:10 Reply Reply with quote Quote
Permalink 0 Jvnto
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).
2015-11-06 23:03 Reply Reply with quote Quote
Permalink 0 Appie
Hi, Tried to apply this on one of my sheets with an SQL connection that is stored in excel as table query with name range Table_query. I can see the CF's applied in my table but the count always results in 0.

=colorfunction(A37;Table_Query;FALSE)

A37 contains the color

any ideas?
Cheers,
Appie
2016-01-13 15:20 Reply Reply with quote Quote
Permalink 0 Appie
Hi Guys,

Thanks for this wonderfull module. I applied it and saved. Funny thing is that it returns 0, i'm applying this on a named_range large table of 50.000 rows i can see the colors in the table but nothing happens in the cell that niets to count it other than 0.

Any help would be appreciated.

Appie
2016-01-13 15:04 Reply Reply with quote Quote
Permalink 0 Appie
Hi, Tried to apply this on one of my sheets with an SQL connection that is stored in excel as table query with name range Table_query. I can see the CF's applied in my table but the count always results in 0.

=colorfunction(A37;Table_Query;FALSE)

A37 contains the color

any ideas?
Cheers,
Appie
2016-01-13 15:16 Reply Reply with quote Quote
Permalink 0 Appie
Hi, Tried to apply this on one of my sheets with an SQL connection that is stored in excel as table query with name range Table_query. I can see the CF's applied in my table but the count always results in 0.

=colorfunction(A37;Table_Query;FALSE)

A37 contains the color

any ideas?
Cheers,
Appie
2016-01-13 15:19 Reply Reply with quote Quote
Permalink 0 Nopchanok
It's really work for me, i use 2007. Thanks a lot.
2016-01-19 08:17 Reply Reply with quote Quote
Permalink 0 S.mantha
Well, added this in as a macro. Some problem with the "v=result" line, excel will not let me enter it. Copied exactly as instructed. Didn't save it, ran it anyway in Excel just to test it. Of course it didn't work. Should I delete the spacing in the formula above? Are the steps supposed to be numbered? (they are not currently).
2016-01-25 17:53 Reply Reply with quote Quote
Permalink 0 Iton
Hi, I tried copying the VBA-formula as indicated above but when trying to apply the formula ColorFunction I simply get "#NAME?". I am using Excel 360. Can it be that the VBA-formula does not work on this Excel-version? Could someone else please have a try?
2016-02-04 13:54 Reply Reply with quote Quote
Permalink 0 Teoh
Thanks for the tip. Very useful. Keep up . Good stuff. Well done : )
2016-03-11 04:54 Reply Reply with quote Quote
Permalink 0 Shaun
wow, got this working but have an issue. I can get the formula to work on the same tab; but it will not work when looking up colours on a different tab. The other tabs contain colours that are generated by conditioning format; has any one got any advice
2016-03-16 15:52 Reply Reply with quote Quote
Permalink 0 Sunil
Yes it has worked and very good. but what about if I have to change the color or reduce the colour cells or increase the cell , the formula don't increase or decrease accordingly as like in pivot we refreseh the pivot and sheet get update according to change in main data.
2016-05-06 14:10 Reply Reply with quote Quote
Permalink 0 @1Ednut
Thank you very much! this worked first time for me!!
wow... i just thought.. "excel should be able to do this" and voila

thank you thank you thank you again!
2016-06-03 16:54 Reply Reply with quote Quote
Permalink 0 Mike
It seems good, but I have an issue, in the code section, you say it does the same thing as the Color Count Function, however, this is not true. It looks at all my fields instead of just the visable ones, I need to be able to code this to look at only the visable cells.

Any help would be great.
2016-06-03 18:43 Reply Reply with quote Quote
Permalink 0 Saifi
Thankyou. very helpful information
2016-09-05 09:52 Reply Reply with quote Quote
Permalink 0 GK
Worked like a charm when other codes would not! THANK YOU!
2016-09-14 20:41 Reply Reply with quote Quote
Permalink 0 Ranj
I have enabled macros to count cell colors. How do add another criteria for text? Lets say I have checked a range and recieved the count for cells that are green. How do I also check how many of those had "D" as text?

thanks!
2016-10-05 19:28 Reply Reply with quote Quote
Permalink 0 Kailas
Thanks! very useful function :)
2016-10-06 12:01 Reply Reply with quote Quote
Permalink 0 GV
Works Great !!!
Thanks for posting!!
2016-10-06 21:38 Reply Reply with quote Quote
Permalink 0 Sylvia
Your code works really well. Thanks. Can you now please help to alter the function of colorfunction so horizontally merged cell is counted as one? I am using the following function:
Function ColorFunction(r Color 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 = WorksheetFuncti on.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

Thanks!
2016-10-21 08:23 Reply Reply with quote Quote
Permalink 0 Akshatha
Hi,

Works Great. Needed a small tweak though. I have some colored empty cells which are also getting counted right now. How can i ignore those cells?

Thanks
2016-10-24 10:27 Reply Reply with quote Quote
Permalink 0 Dazzy
Fantastic! Worked very well.
2016-11-08 16:10 Reply Reply with quote Quote
Permalink 0 Ken
It's not working for me. I'm running Excel 2013 but I keep getting the Ambiguous name detected: colorfunction.
Is there a way to fix this?
2016-11-10 06:32 Reply Reply with quote Quote
Permalink 0 Himmat
Good day,

I have tried the coding provided. However I am using different color themes, which does not provide me with the answers I am seeking as the coding sums colors of a similar background and I need to obtain the sun for different colors. please assist
2016-11-22 13:05 Reply Reply with quote Quote
Permalink 0 Phillip
Hi,

Is there any way to change the code so that instead of calculating a SUM when set to TRUE, it will count the number of cells with numbers or text in?? Ideally I would like if it could count a number over 10,000, but just the COUNT function would be great. I have tried simply changing SUM in the code to COUNT, but it doesn't work.

Any help would be great
2016-12-09 08:07 Reply Reply with quote Quote
Permalink 0 Rod
Lack me words to express my gratitude for your code. You've saved a lot of time, wish a could buy you a beer.

All the best, mate.
2016-12-12 04:11 Reply Reply with quote Quote
Permalink 0 Jacqui
Brilliant! I've been searching for a way to do this. Works perfectly and will save me so much time!!
2016-12-28 20:07 Reply Reply with quote Quote
Permalink 0 AHMER IRFAN
Jazak ALLAH it was very helpful
2017-01-16 06:30 Reply Reply with quote Quote

Add comment


Security code
Refresh