COUNTIF on cell color

Anonymous

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

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

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

End Function

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

Mike H

0 comments

Answer accepted by question author

Anonymous

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:

greater than zero, but less than 11 (i.e. 1-10) (perhaps your RED cells)
greater than 10, but less than 21 (i.e. 11-20) (perhaps your YELLOW cells)
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

0 comments

52 additional answers

Anonymous

2013-08-25T07:08:38+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.
Was this answer helpful?

0 comments No comments
Anonymous

2013-08-25T07:05:49+00:00
Hi,

here is an example without using VBA. You can use the old Excel4 macrofunction GET.CELL.

Example:

Select cell E2.

Open Name-Manager and click New.

Enter the name Color (see figur).

Refers to: =GET.CELL(38,Tabelle1!A2)

Close Name-Manager

Select cell E2

Enter formula: =Color

Copy formula to left and down. You get the nearby color number of the old 56 colors.

Now you can count the color numbers. Formula in cell K2 is: =COUNTIF($E$2:$H$7,J2). Copy it down.

Done!

Regards,

Frank

If this post answers your question, pleaseit for all readers as the Answer.
Dear Sir,

Could you please confirm in the code line " =GET.CELL(38,Tabelle1!A2) " the Tabelle1!A2 seems to be the sheet name. And what is 38 for?

Please confirm. Thanks.
Was this answer helpful?

0 comments No comments
Anonymous

2013-08-01T08:35:24+00:00

This worked - thanks! However, it only worked for a one time count ... wierd. So, if I made a change and highlighted additional cells it did not count those. I had to refresh the formula cell by opening it and pressing enter. Is their a way to fix this? I am a beginner as you may have guessed. And yes, I should not be using 2003 if that matters.

Hi,

I'm afraid there's little you can do about that because changing the colour of a cell doesn't force Excel to re-calculate. You can force calculation by tapping F9.
Was this answer helpful?

0 comments No comments