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

2016-02-09T18:21:28+00:00

Thank you Mike H. This was my solution!
Was this answer helpful?

0 comments No comments
Anonymous

2016-02-24T01:12:25+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.

I have read the entire string but no where does the COUNTIF function show... I need to use it when I filter and it updates based on the COUNTIF criteria.

Example:

Here is the VB:

Function GetCellColor(xlRange As Range)

    Dim indRow, indColumn As Long

    Dim arResults()

    Application.Volatile

    If xlRange Is Nothing Then

        Set xlRange = Application.ThisCell

    End If

    If xlRange.Count > 1 Then

      ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)

       For indRow = 1 To xlRange.Rows.Count

         For indColumn = 1 To xlRange.Columns.Count

           arResults(indRow, indColumn) = xlRange(indRow, indColumn).Interior.Color

         Next

       Next

     GetCellColor = arResults

    Else

     GetCellColor = xlRange.Interior.Color

    End If

End Function

Function GetCellFontColor(xlRange As Range)

    Dim indRow, indColumn As Long

    Dim arResults()

    Application.Volatile

    If xlRange Is Nothing Then

        Set xlRange = Application.ThisCell

    End If

    If xlRange.Count > 1 Then

      ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)

       For indRow = 1 To xlRange.Rows.Count

         For indColumn = 1 To xlRange.Columns.Count

           arResults(indRow, indColumn) = xlRange(indRow, indColumn).Font.Color

         Next

       Next

     GetCellFontColor = arResults

    Else

     GetCellFontColor = xlRange.Font.Color

    End If

End Function

Function CountCellsByColor(rData As Range, cellRefColor As Range) As Long

    Dim indRefColor As Long

    Dim cellCurrent As Range

    Dim cntRes As Long

    Application.Volatile

    cntRes = 0

    indRefColor = cellRefColor.Cells(1, 1).Interior.Color

    For Each cellCurrent In rData

        If indRefColor = cellCurrent.Interior.Color Then

            cntRes = cntRes + 1

        End If

    Next cellCurrent

    CountCellsByColor = cntRes

End Function

Function SumCellsByColor(rData As Range, cellRefColor As Range)

    Dim indRefColor As Long

    Dim cellCurrent As Range

    Dim sumRes

    Application.Volatile

    sumRes = 0

    indRefColor = cellRefColor.Cells(1, 1).Interior.Color

    For Each cellCurrent In rData

        If indRefColor = cellCurrent.Interior.Color Then

            sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)

        End If

    Next cellCurrent

    SumCellsByColor = sumRes

End Function

Function CountCellsByFontColor(rData As Range, cellRefColor As Range) As Long

    Dim indRefColor As Long

    Dim cellCurrent As Range

    Dim cntRes As Long

    Application.Volatile

    cntRes = 0

    indRefColor = cellRefColor.Cells(1, 1).Font.Color

    For Each cellCurrent In rData

        If indRefColor = cellCurrent.Font.Color Then

            cntRes = cntRes + 1

        End If

    Next cellCurrent

    CountCellsByFontColor = cntRes

End Function

Function SumCellsByFontColor(rData As Range, cellRefColor As Range)

    Dim indRefColor As Long

    Dim cellCurrent As Range

    Dim sumRes

    Application.Volatile

    sumRes = 0

    indRefColor = cellRefColor.Cells(1, 1).Font.Color

    For Each cellCurrent In rData

        If indRefColor = cellCurrent.Font.Color Then

            sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)

        End If

    Next cellCurrent

    SumCellsByFontColor = sumRes

End Function

When I use the fomula: "=CountCellsByColor(R6:R3179,R14)" [Column R has three colors: Red non compliant, {r14} Yellow pending and Green compliant] and I use another column to filter by unit (there are 152), what I need is the countCellsByColor to return the COUNTIF for only the visible cells.

On other cells I use:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Z6:Z3179,ROW(Z6:Z3179)-MIN(ROW(Z6:Z3179)),,1)),ISNUMBER(SEARCH("NOT ASSIGNED",Z6:Z3179))+0)

This works for text but I need somthing simular for colors. Either reference the Font Color or Cell Color as in the VB above.

Can you help me, please?
Was this answer helpful?

0 comments No comments
Anonymous

2016-05-06T10:13:22+00:00

Sometimes simple is best - worked for me!
Was this answer helpful?

0 comments No comments