Count Cells by colour formula with count if text

kbendelac

New Member
Joined
Aug 9, 2020
Messages
18
Office Version
  1. 2019
Platform
  1. Windows
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,
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,980
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 9, 2020
Messages
18
Office Version
  1. 2019
Platform
  1. Windows
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
VBA Code:
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,980
Office Version
  1. 365
Platform
  1. Windows
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")

VBA Code:
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

New Member
Joined
Aug 9, 2020
Messages
18
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,980
Office Version
  1. 365
Platform
  1. Windows
That was my bad, I copied the wrong one, the last code was for font colour, not fill colour.
VBA Code:
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

New Member
Joined
Aug 9, 2020
Messages
18
Office Version
  1. 2019
Platform
  1. Windows
Yes, that worked! Amazing! Thank you so much for your help Jason! Much appreciated!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,321
Messages
5,600,948
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top