COUNTIF on cell color

Anonymous
2010-11-01T12:09:13+00:00

Hello,

Within Excel, I have a matrix of programs horizontally and accounts vertically and I have evaluated the performance of each intersection of the maxtrix with red (has concerning issues), yellow (has some issues), and green (no issues). At the bottom of my matrix, I'd like to use a COUNTIF formula to count the number of cells that are red, yellow, and green. What's the best way to COUNTIF on cell color? If needed, I can create a separate matrix that identifies the cell color by number (red,green,blue combination) and then do a lookup on that matrix.

Thanks,

Scott

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2010-11-01T12:16:18+00:00

Hi,

Excel has no native way of doing this so we must create a UDF to do it. ALT+F11 to open vb editor, right click 'ThisWorkbook' and insert module and paste the code below in. Close VB editor.

Back on the worksheet call with

=Countcolour($A$1:$J$1,K1)

Where:-

A1:J1 is the range you want to count

K1 is a cell with the fill colour you want to count.

Function Countcolour(rng As Range, colour As Range) As Long

Dim c as Range

Application.Volatile

For Each c In rng

    If c.Interior.ColorIndex = colour.Interior.ColorIndex Then

        Countcolour = Countcolour + 1

    End If

Next

End Function

If this post answers your question, please mark it as the Answer.

Mike H

Was this answer helpful?

200+ people found this answer helpful.
0 comments
Answer accepted by question author
Anonymous
2010-11-01T12:34:16+00:00

To expand on HansV (and MikeH's) comments... You might consider using the same criteria that you used to set the colors to count them.  If you used conditional formatting to color them, then use the same criteria to get a count of them using either SUMPRODUCT() [works for all versions of Excel], or COUNTIFS() [Excel 2007 & later only] to get a count.

Here are 3 example formulas assuming a list of integers in cells from A2:A7 that you want to count the cells in that are:

  1. greater than zero, but less than 11 (i.e. 1-10) (perhaps your RED cells)
  2. greater than 10, but less than 21 (i.e. 11-20) (perhaps your YELLOW cells)
  3. greater than 20, but less than 31 (i.e. 21-30) (perhaps your GREEN cells)

Using SUMPRODUCT():

=SUMPRODUCT(--(A2:A7>0),--(A2:A7<11))

=SUMPRODUCT(--(A2:A7>10),--(A2:A7<21))

=SUMPRODUCT(--(A2:A7>20),--(A2:A7<31))

Equivalent using SUMIFS()

=COUNTIFS(A2:A7,">0",A2:A7,"<11")

=COUNTIFS(A2:A7,">10",A2:A7,"<21")

=COUNTIFS(A2:A7,">20",A2:A7,"<31")

I am free because I know that I alone am morally responsible for everything I do. R.A. Heinlein

Was this answer helpful?

50+ people found this answer helpful.
0 comments

52 additional answers

Sort by: Newest
Most helpful Newest Oldest
  1. HansV 462.7K Reputation points MVP Volunteer Moderator
    2017-04-09T09:57:18+00:00

    Let's say your range is A1:A100.

    Number of red cells: =SUM(COUNTIF(A1:A100,{"zyz","zzy","yyz"}))

    Number of yellow cells: =SUM(COUNTIF(A1:A100,{"zyx","xxz","xzz"}))

    Number of green cells: =SUM(COUNTIF(A1:A100,{"xzy","yyx","yxx"}))

    Was this answer helpful?

    0 comments
  2. Anonymous
    2017-04-09T04:59:49+00:00

    To expand on HansV (and MikeH's) comments... You might consider using the same criteria that you used to set the colors to count them.  If you used conditional formatting to color them, then use the same criteria to get a count of them using either SUMPRODUCT() [works for all versions of Excel], or COUNTIFS() [Excel 2007 & later only] to get a count.

    Here are 3 example formulas assuming a list of integers in cells from A2:A7 that you want to count the cells in that are:

    1. greater than zero, but less than 11 (i.e. 1-10) (perhaps your RED cells)
    2. greater than 10, but less than 21 (i.e. 11-20) (perhaps your YELLOW cells)
    3. greater than 20, but less than 31 (i.e. 21-30) (perhaps your GREEN cells)

    Using SUMPRODUCT():

    =SUMPRODUCT(--(A2:A7>0),--(A2:A7<11))

    =SUMPRODUCT(--(A2:A7>10),--(A2:A7<21))

    =SUMPRODUCT(--(A2:A7>20),--(A2:A7<31))

    Equivalent using SUMIFS()

    =COUNTIFS(A2:A7,">0",A2:A7,"<11")

    =COUNTIFS(A2:A7,">10",A2:A7,"<21")

    =COUNTIFS(A2:A7,">20",A2:A7,"<31")

    I am free because I know that I alone am morally responsible for everything I do. R.A. Heinlein

    What to do if I set conditions for a text.

    eg. for zyz = RED, zzy = RED, YYZ = RED, zyx = YELLOW, xxz = YELLOW, xzz = YELLOW and xzy = GREEN, yyx = GREEN, yxx = GREEN. ???

    Was this answer helpful?

    0 comments
  3. HansV 462.7K Reputation points MVP Volunteer Moderator
    2016-06-16T14:06:50+00:00

    This version of MikeH's function will sum the values instead of counting them:

    Function SumColour(rng As Range, colour As Range) As Long

        Dim c As Range

        Application.Volatile

        For Each c In rng

            If c.Interior.ColorIndex = colour.Interior.ColorIndex Then

                If IsNumeric(c.Value) Then

                    SumColour = SumColour + c.Value

                End If

            End If

        Next c

    End Function

    Like the SUM function, SumColour ignores cells with text values.

    Was this answer helpful?

    0 comments