Count by color

With the built-in COUNTA()-function it's not possible to count cells in a range depending on each cells background color. With the custom function below you can count the cells depending on their background color:

Function CountByColor(InputRange As Range, ColorRange as Range) As Long
Dim cl As Range, TempCount As Long, ColorIndex As Integer
    ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex
    TempCount = 0
    For Each cl In InputRange.Cells
        If cl.Interior.ColorIndex = ColorIndex Then 
            TempCount = TempCount + 1
        End If
    Next cl
    Set cl = Nothing
    CountByColor = TempCount
End Function

This function is used in the same way as built-in worksheetfunctions. InputRange is the range that the function is going to count, ColorRange is a reference to a cell containing the background color you wish to count.
E.g. used like this in a worksheet formula: =CountByColor(A1:A100,C1)

In Excel 5/95 the variables InputRange and ColorRange must be declared as Variant instead of Range. This function can easily be adapted for use with other worksheetfunctions that you wish to use on cells with different background colors.

Document last updated 1999-08-14 12:44:24 Printerfriendly version

User comments:

Ole P. from Norway wrote (2006-05-11 09:53:55 CET):

Re: How I aplicate the function count by color?
This page contains information about how to use the macro examples.

Alfonso Vargas from Santiago, Chile wrote (2006-05-11 00:29:48 CET):

How I aplicate the function count by color?
Hi:

I'm work in an sheet Excel for control maintenance program
of services. But I dont know employ for count the cells

Tank You

Ole P. from Norway wrote (2005-03-04 23:54:54 CET):

Re: Count Cells By Color By Initial - That's What I Need
Such a function does not exist, but you can modify the function example above to make one like this:

If cl.Interior.ColorIndex = ColorIndex Then
  If cl.Value = "INITIALS" Then
    TempCount = TempCount + 1
  End If
End If

Jim Davis from Albion, INDIANA wrote (2005-03-04 20:34:51 CET):

Count Cells By Color By Initial - That's What I Need
Is there a formula/function to use to count the number of times a person's initials shows up in a Green Cell or maybe a Red Cell, or perhaps a Yellow Cell within a range or cells.

Ole P. from Norway wrote (2004-08-29 13:37:22 CET):

Re: Counting Under Conditional Formatting
Hi!

Chip Pearson has a solution for this problem.

Alexandre Correa from Oliveira / MG / Brazil wrote (2004-08-28 03:46:44 CET):

Counting Under Conditional Formatting
Hello, EDC!

This site is a "must have" on my favorites!

I'm in trouble trying to count background-color cells under conditional formatting! I've learned that it is not possible with the normal functions provided with Excel.

But it change the colors... so it must have a way to do this...
Can you help us on this challenge?

I'm a Discussion-Forum member - at ExcelBr - from Brazil.
No one of their 2100 members could post any solution about it.

Regards,

Alexandre Correa
reiscorrea@yahoo.com.br