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

2017-09-25T16:36:37+00:00

I have the following Module as a VBAProject

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)

    Dim rCell As Range

    Dim lCol As Long

    Dim vResult

''''''''''''''''''''''''''''''''''''''

'Sums or counts cells based on a specified fill color.

'''''''''''''''''''''''''''''''''''''''

    lCol = rColor.Interior.ColorIndex

    If SUM = True Then

        For Each rCell In rRange

            If rCell.Interior.ColorIndex = lCol Then

                vResult = WorksheetFunction.SUM(rCell, vResult)

            End If

        Next rCell

    Else

        For Each rCell In rRange

            If rCell.Interior.ColorIndex = lCol Then

                vResult = 1 + vResult

            End If

        Next rCell

    End If

   ColorFunction = vResult

End Function

and back in my excel sheet I have the following expression in the cell where I want the resulting count shown (cell Q2 contains the reference color used throughout the cells $B$21:$AF$343)

=ColorFunction($Q2,$B$21:$AF$343,FALSE)
Was this answer helpful?

0 comments No comments
Anonymous

2017-09-24T16:18:22+00:00

JulieDunham,

The functions provided only count the cell color if it has been formatted the normal way. It does not recognize colors produced by conditional formatting. However, if you applied a rule to produce the conditional formatting, you can use the normal countif or countifs (for multiple conditions) to count the grades

=countif(A:A,"B")

would count the cells in column A that contain just the letter B.

So you wouldn't need VBA.

--

Regards,

Tom Ogilvy
Was this answer helpful?

0 comments No comments
Anonymous

2017-09-24T16:12:58+00:00

Julie,

re: count cells with conditional formatting (CF)

The code you refer to, by Mike H., will not count cells with CF colors.

CF adds an additional color layer over the top of any existing color.

Counting cells with CF is complicated, requires extra code and takes longer to run.

You probably should make a new post and not piggyback on one that is 7 years old.

Older posts with multiple replies are often ignored by people who can provide help.

'---

Jim Cone

Portland, Oregon USA

https://goo.gl/IUQUN2 (Dropbox)

(free & commercial excel add-ins & workbooks)
Was this answer helpful?

0 comments No comments