Count Cells by colour formula with count if text

[kbendelac]

Hi,
I am trying to use the following formula to count colour coded cells in a range that contain text "current period or prior period" and the formula does not seem to work.

Countif(CountCellsByColor($G$2:$G$159,G164),”current period”)

any assistance would be appreciated.
Thank you,

 

[jasonb75]

Count cells by colour would be a udf, so it would need some vba code added to your workbook, there are probably a few variations of this so we would need to see the code before being able to identify the exact problem.

Most likely it returns results as an array instead of a range, which means that it will not work with countif (or similar functions).

 

[kbendelac]

Hi, I have already added the following VBA code

How to count and sum cells by color in Excel How to count by color and sum by color in Excel 2016, 2019, and 2013

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

 

[jasonb75]

You have about 6 different functions in that code, here's a slight edit to the one you asked about. Enter the formula as

=CountCellsByColor($G$2:$G$159,G164,"current period")

Function CountCellsByFontColor(rData As Range, cellRefColor As Range, crit As String) 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
            If LCase(cellCurrent.Value) = LCase(crit) Then
                cntRes = cntRes + 1
            End If
        End If
    Next cellCurrent

    CountCellsByFontColor = cntRes
End Function

 

[kbendelac]

Hi,
It's still not working. It returns as #VALUE!

 

[jasonb75]

That was my bad, I copied the wrong one, the last code was for font colour, not fill colour.

unction CountCellsByColor(rData As Range, cellRefColor As Range, crit As String) 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
            If LCase(cellCurrent.Value) =  LCase(crit) Then
                cntRes = cntRes + 1
            End If
        End If
    Next cellCurrent

    CountCellsByColor = cntRes
End Function

 

[kbendelac]

Yes, that worked! Amazing! Thank you so much for your help Jason! Much appreciated!