How Many Background Colors Are In Excel

How to Count Colored Cells in Excel – A Footstep past Pace Tutorial + Video

-- Past Sumit Bansal

Watch Video – How to Count Colored Cells in Excel

Wouldn't it be great if there was a function that could count colored cells in Excel?

Sadly, at that place isn't any inbuilt function to do this.

BUT..

It can easily be washed.

How to Count Colored Cells in Excel
- #1 Count Colored Cells Using Filter and SUBTOTAL
- #2 Count Colored Cells Using Become.CELL Function
  - Creating a Named Range
  - Getting the Color Code for Each Cell
  - Count Colored Cells using the Colour Lawmaking
- #three Count Colored Using VBA (past Creating a Custom Function)

How to Count Colored Cells in Excel

In this tutorial, I will bear witness you three means to count colored cells in Excel (with and without VBA):

Using Filter and SUBTOTAL function
Using Become.CELL part
Using a Custom Function created using VBA

#1 Count Colored Cells Using Filter and SUBTOTAL

To count colored cells in Excel, you lot need to utilise the post-obit two steps:

Filter colored cells
Use the SUBTOTAL function to count colored cells that are visible (after filtering).

Suppose you lot accept a dataset as shown below:

There are two background colors used in this data ready (green and orange).

Here are the steps count colored cells in Excel:

In any cell below the data set, use the following formula:=SUBTOTAL(102,E1:E20)
Select the headers.
Go to Data –> Sort and Filter –> Filter. This will apply a filter to all the headers.
Click on whatsoever of the filter drib-downs.
Go to 'Filter by Color' and select the color. In the to a higher place dataset, since at that place are two colors used for highlighting the cells, the filter shows two colors to filter these cells.

As soon equally you filter the cells, you will notice that the value in the SUBTOTAL role changes and returns only the number of cells that are visible after filtering.

How does this work?

The SUBTOTAL office uses 102 as the first argument, which is used to count visible cells (hidden rows are not counted) in the specified range.

If the data if non filtered it returns 19, but if it is filtered, then it only returns the count of the visible cells.

Try information technology Yourself.. Download the Example File

#2 Count Colored Cells Using GET.Prison cell Function

GET.CELL is a Macro4 function that has been kept due to compatibility reasons.

It does not work if used as regular functions in the worksheet.

However, it works in Excel named ranges.

See As well : Know more nearly Become.CELL office.

Here are the three steps to utilise Get.CELL to count colored cells in Excel:

Create a Named Range using GET.Jail cell function
Use the Named Range to get color code in a cavalcade
Using the Color Number to Count the number of Colored Cells (past color)

Let's deep dive and see what to exercise in each of the three mentioned steps.

Creating a Named Range

Getting the Colour Lawmaking for Each Prison cell

In the jail cell adjacent to the data, apply the formula =GetColor

This formula would return 0 if there is NO background colour in a cell and would return a specific number if there is a background color.

This number is specific to a colour, so all the cells with the same background color get the aforementioned number.

Count Colored Cells using the Colour Code

If y'all follow the in a higher place process, y'all would accept a cavalcade with numbers respective to the groundwork color in it.

To become the count of a specific colour:

Somewhere below the dataset, requite the aforementioned groundwork color to a cell that you want to count. Make sure y'all are doing this in the same column that you used in creating the named range. For example, I used Column A, and hence I will apply the cells in column 'A' only.
In the adjacent cell, use the following formula:

=COUNTIF($F$2:$F$20,GetColor)

This formula volition requite yous the count of all the cells with the specified groundwork color.

How Does It Work?

The COUNTIF function uses the named range (GetColor) as the criteria. The named range in the formula refers to the next jail cell on the left (in column A) and returns the color code for that cell. Hence, this color code number is the criteria.

The COUNTIF function uses the range ($F$2:$F$18) which holds the colour code numbers of all the cells and returns the count based on the criteria number.

Endeavor it Yourself.. Download the Case File

#3 Count Colored Using VBA (by Creating a Custom Function)

In the above 2 methods, you learned how to count colored cells without using VBA.

Only, if y'all are fine with using VBA, this is the easiest of the iii methods.

Using VBA, we would create a custom function, that would work like a COUNTIF function and render the count of cells with the specific background colour.

Here is the lawmaking:

            'Code created by Sumit Bansal from https://trumpexcel.com Part GetColorCount(CountRange As Range, CountColor Equally Range) Dim CountColorValue Every bit Integer Dim TotalCount As Integer CountColorValue = CountColor.Interior.ColorIndex Set rCell = CountRange For Each rCell In CountRange   If rCell.Interior.ColorIndex = CountColorValue Then     TotalCount = TotalCount + ane   End If Next rCell GetColorCount = TotalCount End Function

To create this custom function:

To use this role, simply use it as any regular excel role.

Syntax: =GetColorCount(CountRange, CountColor)

CountRange: the range in which you lot want to count the cells with the specified groundwork color.
CountColor: the color for which you want to count the cells.

To apply this formula, utilize the same background color (that you want to count) in a jail cell and employ the formula. CountColor argument would be the same cell where you are entering the formula (equally shown below):

Note: Since there is a code in the workbook, save it with a .xls or .xlsm extension.

Try it Yourself.. Download the Example File

Practise you know whatsoever other way to count colored cells in Excel?

If yes, do share it with me by leaving a comment.

You May Also Like the Following Excel Tutorials:

Count Cells that Contain Text
How to Sum by Color in Excel (Formula & VBA)
Filter Cells with Bold Font Formatting
How to Format Partial Text Strings using VBA
Highlight EVERY Other ROW in Excel (using provisional formatting).
How to Quickly Highlight Blank Cells in Excel.
How to Compare Ii Columns in Excel.

Excel Ebook Subscribe

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

67 thoughts on "How to Count Colored Cells in Excel – A Step by Pace Tutorial + Video"

I'thou noticing that the VBA method doesn't work with conditional formatting of groundwork colors. Whatsoever workaround for lid?
- I accept the same issue
Hi, I am using the VBA module to count preferred meeting session times, information technology was working OK – until I added a separate VBA module called CounxlDiagonalDown to count diagonal borders (people who have indicated they are non attending said meeting), and now I am getting a #NAME? syntax error on the GetColorCount. any suggestions on a fix? Thanks
Thank you, your VB macro works perfectly!!
Groovy stuff, but the VB does not work on conditional formatting.. merely if y'all manually change the background color of cells. Have whatsoever thought to count backgrounds with conditional formating?
Thank you so much. Just what I needed.
Great vba function! Very useful. I change the 'interior' to 'font' and it works nicely when counting range with different font colors. However, I tried it to provisional formatting and information technology didn't piece of work. Any thought, why?
Does this work with horizontal range. Doesnt seem to be working in mine with an horizontal range
Fantastic video and article. Very helpful and simple to follow. Give thanks you!
Simple and direct forward. This did the task. Cheers!
I accept a set of two ranges with compassion:
Say my A2:A10 with data of
B2:B10 with data of
I applied role in C2 and draged upto C10<=if(isna(Friction match($A$ii:$A$ten,$B$2:$B$ten,0)),A2," ")
Which requite me the result every bit (this consequence says non shared number of Fix A2:A10 to B2:B10)
Now I coloured Full set of A2:A10 and also B4 and B9 as yellow and I need the out put in C2 to C10 every bit

Tin can any body help me to fix this problem
(VBA Solution)
Also good to be truthful…merely throws the standard formula fault ("non trying to type a formula etc."
Give thanks you so much, this solve my problems.
The code in the screen shot of the VBA has errors
Help! Is there anyway to modify this VBA and then that it works for =max
GOOD TRICKS FOR COUNTING BY COLOUR
tried =SUBTOTAL(102,$E$2:$E$20) and didn't work. waste of time
Is at that place a manner to count the cells past color but also having a criteria?. For case I want to count the cells in a column that are colored grey just only the ones that have an specific value on them like having the word "unique" in the prison cell.

I would really appreciate the help thanks
- I know this is kind of a ghetto solution, but:
  I just copied the unique cells I needed to a different worksheet and did the function there, information technology worked perfectly
Great code. Notwithstanding, it does not automatically update, unless I click on the cell each time. Please note, I have excel fix to automatically update calculations, then not certain why not working.
The VBA function worked bully!! I'm now showing off to anyone in the part who will listen! Thanks very much
Hi! I cannot become my go.prison cell (38,…) to work. Is at that place any adventure I could contact you?
- Can you please try with ; instead of , ? It work for me.
Using VBA was perfect! Thanks
I'one thousand using your VBA method, and information technology works but fine, merely is there any mode to make it piece of work for cells that take RGB colour values instead of the standard ones from the color palette? Using .ColorIndex only works for those types of colors
Hullo,
The function choice works fine, there is only 1 thing. As soon as i of the colours changes the sum won't refresh, is at that place an way to sort this out ?
- Delete the module > salvage > close and reopen file > add the module again > click on any cell with the formula > press "enter"
- Button F2.
  Button Enter.
Is there a way to apply this to rows rather than columns?!
The VBA formula worked bang-up on my spreadsheet. But, I saved it and re-opened and now all the cells where I had formulas say #Name?. I checked and the code is yet in the module. It comes up in the listing when I begin to blazon =getcolorcount. I tried removing the Code and re-inserting and tin't get the formula to work any longer. I'm using Excel 2010, is at that place any reason this suddenly wouldn't piece of work now?

Please assistance – this formula was perfect for my spreadsheet for work because I didn't want to have to add another cavalcade to the spreadsheet to get this to work.

Thanks,
Jana
Does this work in an online excel sheet?
Hi,
Thanks for the VBA code, I used your code and it works perfect – information technology counts the colored cells, but it does not update automatically. If a new cell is colored, everyctime I accept to click on the formula to have it update the count.
Is at that place any other style to get this automated when a new cell is colored?
Appreciate your response
I've used your VB method and it works great, cheers and then much! I exercise have an outcome though that I'yard having troubles resolving…

I'k using your formula as office of a calendar to runway equipment utilization by filling the cells with color when the equipment is used. The problem I'thou having is that the formula does not automatically recalculate when new colored cells are entered. It does recalculate when you click on the cell containing the formula and printing enter, but I've got hundreds of assets that I'thousand tracking and that's not an efficient manner of doing information technology.

I've tried all the usual, F9, recalculate formula, recalculate worksheet, etc. null works. I've even recorded Macros of actually highlighting all formula cells and clicking enter. Information technology works when I practice information technology, but the macro returns a value error when used.

Do yous have a work around for this or some other VB Macro that tin exist assigned to a radio button to recalculate the colored cell totals each fourth dimension the agenda is updated?
- Ctrl-Alt-F9
I institute this article very useful, yet the VBA functions didn't work. I take a tabular array with various information, I used the conditional formatting in one column and built 5 rules and based on that cells have been colored in greed, cerise and yellow…any thoughts?
- I take come up beyond 5 functions that count coloured cells such as this code, and none of them work on conditional formatting.
I accept used your following code and information technology works perfectly:

Function GetColorCount(CountRange As Range, CountColor As Range)
Dim CountColorValue Every bit Integer
Dim TotalCount Every bit Integer
CountColorValue = CountColor.Interior.ColorIndex
Set rCell = CountRange
For Each rCell In CountRange
If rCell.Interior.ColorIndex = CountColorValue Then
TotalCount = TotalCount + ane
Finish If
Next rCell
GetColorCount = TotalCount
End Function

Just, now I want to exercise a little more. For the range of cells, I want to accept four split functions. one) Count cells IF they are a item color, as well every bit ending in "*o" 2) Count cells IF they are a particular color, as well as ending in "*s" three) Count cells IF they are a detail colour, as well equally having 2 text characters in the cell, "??" and 4) Count cells IF they are a item color, besides equally having a number greater than goose egg in the cell, ">0". How tin can I alter the base of operations "GetColorCount" lawmaking to incorporate this boosted parameter for each example?
I tried using your 3rd option simply I am thinking that I am not right in doing so. Here is what I take and what I am trying to exercise. I take a spreadsheet with my supervisors and their clock-in times that I export from our schedule and then copy & paste into workbook. I color each row based on if they were on time, late but the fourth dimension rolled back (7 minute grace period) and late only jumped forrad 1/iv hour. I then filter past the supervisor's last name and then by the "ErrorLog" header, checking simply CLOCK IN & Late CLOCK IN. The "Clock In" choice could exist either greenish (on fourth dimension) or orangish (tardily but rolled back) and and then Late Clock In is yellow. The unabridged ErrorLog column is K10:K118, for all supervisors. Obviously when I sort by both Last Proper name and ErrorLog headers information technology reduces the number of rows and hides all the balance. I but want the formula to count each color that is visible. Is what I am doing fifty-fifty possible? I want to be able to alter up the filter a bit by changing the Last Name so that I tin but encounter each supervisor individually.
Nifty video…. I exercise have a question for you lot… I used your code and it works perfect except for one thing, it counts the colored cells, but it does non update automatically. If a new cell is colored, I have to click on the formula to have information technology update the count. Is there a lawmaking I tin add to have information technology update automatically once a new colored cell is added? Thanks over again for a great video!!!
Your VBA solution works Only Not with colors from "Provisional Formating."

I have 17 cells in a column, all under a conditional formatting to turn the cell color "low-cal ruby-red" if a certain condition is met. There are only 3 cells that are "light blood-red" (coming together the condition) merely your VBA script returns an answer of "17", pregnant it considers all cells "light ruddy". Then I manually went in and colored one jail cell (not already highlighted by the provisional formatting) blue, and your VBA returned an respond of "sixteen". Clearly then, information technology does not recognize the results of conditional formatting, only "manually entered" colors.

Whatsoever solution? This is disquisitional as the colored cells volition be different depending on what conditions are met. I demand a way to count them per each condition.

(I learned a lot virtually adding a custom VBA code. Thanks!)
- Same issue! Any suggestions?
- Was there whatever answer to this question? thanks.
Hi,
can you use both GetColorCount and Sumbycolor VBA in the same worksheet.
Can yous please explain what is 38 mentioned in the proper noun manager formula? What does information technology relate to?
- If yous follow the link to more information most Get.Prison cell it tells you, I was wondering the same thing.
When i press 'enter' with you'r download exel i get #NAME? why? PS ; I am new in VBA
Hi, please help. if the cell is in cf it doesn't count. i am using as well the standard cf for date occuring.
- Hey Mart.. Yes this doesn't work when conditional formatting is in play. IF you want to count based on CF rules, you can utilise the same rule you take used to apply conditional formatting and count the total values. For example, if you utilise CF to highlight cells that contain "Yes", then use a COUNTIF role to count all these cells.
How tin can you reach the aforementioned matter except counting cells in a row versus column (as outlined here)? For example, I want to know how many of each color (Red, Yellowish, Dark-green, and no color) are in row ii (range – A2:I2).
Side notation/outcome: The Subtotal function seems to but count cells that have something in them. I would like to count cells that are just a color without any text in the cell. Is that possible?
Thank you for your help.
- I am looking for this answer likewise. I need the count of cells from a bigger range a2:bz52. And some cells are merged. Is there a way for this? Thank you in advance! Zita
I liked the VBA option, works similar a charm. However if my data range is non-continuous , for example, instead of A1:A10, I need to count the cell color in A1, A4, A9, How should I modify the formula? Thanks.
- Hi Azz,
  Enclose your non-continuous range within brackets inside the function, (10, y, z, a:c), so for your example, you would utilise:
  
  =GetColorCount( ( A1, A4, A9 ), 50 )
  
  Where,
  50 = Green box color index.
If declared strictly and with names more highly-seasoned (IMO ;)). Cheers for the code and the idea backside

Public Function GetColorCount(ByRef Target As Excel.Range, ByRef rgColor As Excel.Range) As Long
Dim rCell As Excel.Range
Dim Color Equally Long
Dim lgCounter As Long

Color = rgColor.Interior.ColorIndex
For Each rCell In Target
If rCell.Interior.ColorIndex = Color Then lgCounter = lgCounter + 1
Next rCell
GetColorCount = lgCounter
End Office
Hullo,

I started using your Count Cells Based on Background Color in Excel #3 using VBA. This wors very skillful. Thanks for information technology.
But at present I have a trouble using the same function by checking cells where the background color is ready past Provisional Formatting. I am checking for dates older than today() and marker these cells in crimson groundwork; works well using provisional formatting but the count doesn't work for these cells.
thanks for any thought or hint
- Howdy Nico,
  
  Do you lot use a formula in your provisional formatting (CF)?
  If you do, it returns TRUE and you are coloring the cells that meet that status. This means that if y'all put the CF formula into a COUNTIF formula, you can count the cells that see the CF weather!
  
  Gr,
  Raymond.
  - Hullo Raymond,
    
    No I'm not using a formula. I use a standard rule type: Cell Value "less than or equal to" =$A$1
    In A+ I only update the day =today().
    thx
    Nico
    - Nico,
      
      I did a test and I realised I needed a help cavalcade to do the job.
      
      Say that range A2:A10 has your values where y'all utilise the CF to.
      Formula in help column B:
      in B2 put '=OR(A2<$A$1,A2=$A$1)', copy down and yous volition get a couple of TRUEs or FALSEs.
      
      The count formula anywhere can and so exist: =COUNTIF(B2:B10,True).
      
      Or y'all can just utilise an IF formula in the help column, giving you i or 0 back. Then a uncomplicated SUM somewhere and in that location you are.
      
      I must exist do-able without the aid cavalcade but I don't know how (yet)!
      
      Raymond
      - Hi Raymond,
        
        A assist column isn't very usefull and would need many boosted help columns, and then I searched and searched.
        On http://www.excel-inside.de (a german Excel & VBA site) I constitute an example using .Font.ColorIndex . With this data I found .Interior.ColorIndex and my solar day was fabricated 🙂
        
        Next footstep: searching for the color indexes on https://msdn.microsoft.com/en-us/library/office/ff840443.aspx
        
        and information technology seems to work – refreshing manually afterward changes – but it works:
        'Office ColorRed(Surface area As Range)
        'ColorRed = 0
        'For Each prison cell In Expanse
        'If cell.Interior.ColorIndex = iii Then
        'ColorRed = ColorRed + cell.Count
        'Cease If
        'Adjacent
        'Cease Role
        Nico
      - Hi Nico,
        
        Thanks for the update and likewise for yous providing the resources that led to the solution you found!
        
        An UDF (User Divers Part) is harmless in your case because yous are using it in only one prison cell to get the count. So that'due south great.
        In complex workbooks, where formulas must be in several rows, UDF'south are not recommanded unless no other choice because they brand the workbook tiresome.
        And every bit they say, at that place is always another way, a unproblematic, faster and stronger way…
        
        I found a non-vba solution: an assortment formula.
        
        Put the following formula adapted to your needs where you want your count.
        In this example, I presume the IF statement is the same as in your conditional format that gives the cells a red color when True. The formula says:
        for each cell in range A2:A10, if the cell value is equal or less than the value of A1, give 1, otherwise give 0. Then give a sum of all the individual results.
        I've used i IF with an OR but information technology didn't work and then information technology became 2 IF'southward.
        
        Instead of just pressing ENTER, press CTRL+SHIFT+ENTER to brand it an array formula, which you lot can recognize by the { } that Excel automatically puts effectually information technology:
        =SUM(IF(A2:A10<$A$1,1,IF(A2:A10=$A$1,1,0)))
        
        And then afterward CTRL+SHIFT+ENTER, you volition see:
        {=SUM(IF(A2:A10<$A$1,ane,IF(A2:A10=$A$i,one,0)))}
        
        No more transmission refresh!
        
        More about the topic:
        http://www.cpearson.com/excel/arrayformulas.aspx
        
        Gr,
        Raymond.
      - i am not a vbe user just this explanation was clear enough to take the swoop;
        so i tried solution number 3 and information technology works!
        however, how can i make the sheet update the numbers after i changed the color of a prison cell? at this moment i need to get to this jail cell en striking enter;
        in that location must be a 'trick' to do this on my imac!
      - Howdy Cezi,
        
        The just workaround I could came up with involves 2 steps:
        [i] Put 'Application.Volatile True' at the beginning of the the function code
        [ii] Put the post-obit code in the worksheet event area (it assumes that y'all want an update if you select any prison cell in range [A1:A10]; please adjust appropriately):
        
        Individual Sub Worksheet_SelectionChange(ByVal Target As Range)
        'Application.StatusBar = False
        On Fault Resume Side by side 'To avoid error if the selection isn't in [A1:A10]
        If Target.Address = Intersect(Target, Range("A1:A10")).Address Then
        ActiveSheet.Calculate
        'Application.StatusBar = "Calculated"
        Stop If
        Finish Sub
        
        Gr,
        Raymond.
      - Hello Raymond, I am having the same issue but am not exactly sure how to follow the workaround you posted.
        
        [1] Should I paste 'Application.Volatile True' subsequently the following row?
        
        'Function GetColorCount(CountRange As Range, CountColor Every bit Range)'
        
        [ii] Where should I put the code you have entered? I'thou afraid I don't know what the "worksheet event area" is or where I can find information technology.
        
        Thank you for your patience and a great guide!
        
        /Kara
      - Raymond,
        
        Your code for the workaround however disables copying and pasting? Practice y'all know how to fix this?
        
        -Amy
      - Hi Amy,
        
        Sorry well-nigh that. Yes, there is a set. The showtime line of code should exist:
        If Application.CutCopyMode And then Exit Sub
        
        So, the terminal worksheet 'SelectionChange' issue should look similar this:
        [CODE]
        Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        'If we are copying, exercise nothing and get out
        If Application.CutCopyMode Then Exit Sub
        
        On Error Resume Next 'To avoid error if the pick isn't in [A1:A10]
        If Target.Accost = Intersect(Target, Range("A1:A10")).Address Then
        ActiveSheet.Calculate
        End If
        Terminate Sub
        [END Lawmaking]
        
        -Raymond