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: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2013-08-25T09:03:52+00:00

    Hi Mike H,

    Many thanks for this code below.

    Please can you tell me what I need to do to the vb code to amend is so that it will count the colours after conditional formatting has taken place?

    Basically, all cells are red, unless they have been conditionally formatted to Green, Yellow or Orange... And this function counts only the red squares.

    For example, I have a range of 30 cells (A3 to AG3) which all start red, but after formatting I have 8 green, 1 yellow, 10 orange and 11 red.. When I use the function to count for each colour, I get 0 for all except the red which gives 30.

    Any ideas?

    Thanks

    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$14,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

    Hello Sir,

    I tried this but once I close the file, the VB code goes off. I want a system where I update the colour cells everyday with different colours in that case, Do i have to paste the code again and again by pressing ALT+F11? seems difficult. Please suggest any other ways.

    Waiting for answer. Thanks.

    If you use this code you must save you workbook as  'macro enabled' with a .XLSM extension. If you save as .XLSX the code wont be saved when you close.

    Was this answer helpful?

    0 comments
  2. HansV 462.7K Reputation points MVP Volunteer Moderator
    2013-08-25T10:02:05+00:00

    In the formula =GET.CELL(38,Tabelle1!A2), GET.CELL is an Excel 4.0 macro function. You cannot use macro function in a cell formula, but you can use them in defined names.

    GET.CELL has two arguments: a number that specifies the type of information that you want, and a reference to a cell.

    The number in the first argument can range from 1 to 66. Using 38 means that GET.CELL returns "Shade foreground color as a number in the range 1 to 56. If color is automatic, returns 0."

    Tabelle1!A2 is the cell reference: it refers to cell A2 on a sheet named Tabelle1. You should replace this with the sheet name and the cell you want to work with. For instance, if you want to refer to the color index of cell D2 on a sheet named Expenses, use =GET.CELL(38,Expenses!D2)

    Was this answer helpful?

    0 comments
  3. Anonymous
    2014-04-04T14:18:04+00:00

    Mike,

    This is my first time I am successful in using an instruction about the use of UDF. Thank you for your clear instruction.

    I have one extra question and has to do with the automatic execution of the color count function. I tried your example and works each time I execute the function but if I add color to another cell the count is not update automatically. How can I do that?

    Thank you in advance

    Jorge C

    Was this answer helpful?

    0 comments