COUNTIF formula to look up text values and the colour of the cell

You would have to create a custom fiction with vba to count cells by colour. It works by comparing them to the colour of another cell, so you would need to have an input cell where you set the background colour to the one you want to find. Google 'Excel count cells by colour'.

Hi,

Little painful, but non-vba solution is...

Suppose your data in column B, add a helper column (e.g. column C) with any value (say 1) to your highlighted cell.
Now use:
=COUNTIFS(B3:B10,"XY",C3:C10,1)
adjust the range to suite.

Blessing.

module1:

'---------------------------------------------------------------------
' ColorIndex Function
'---------------------------------------------------------------------
' Function:    Returns the colorindex of the supplied range
' Synopsis:    Initially, gets a colorindex value for black and white
'              from the activeworkbook colour palette
'              Then works through each cell in  the supplied range and
'              determines the colorindex, and adds to array
'              Finishes by returning acumulated array
' Variations:  Determines cell colour (interior) or text colour (font)
'              Default is cell colour
' Constraints: Does not count colours set by conditional formatting
'---------------------------------------------------------------------
' Author:      Bob Phillips
'              Additions for ranges suggested by Harlan Grove
'---------------------------------------------------------------------


'---------------------------------------------------------------------
Function ColorIndex(rng As Range, _
                    Optional text As Boolean = False) As Variant
'---------------------------------------------------------------------
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim iWhite As Long, iBlack As Long
Dim aryColours As Variant

    If rng.Areas.Count > 1 Then
        ColorIndex = CVErr(xlErrValue)
        Exit Function
    End If

    iWhite = WhiteColorindex(rng.Worksheet.Parent)
    iBlack = BlackColorindex(rng.Worksheet.Parent)

    If rng.Cells.Count = 1 Then
        If text Then
            aryColours = DecodeColorIndex(rng, True, iBlack)
        Else
            aryColours = DecodeColorIndex(rng, False, iWhite)
        End If

    Else
        aryColours = rng.Value
        i = 0

        For Each row In rng.Rows
            i = i + 1
            j = 0

            For Each cell In row.Cells
                j = j + 1

                If text Then
                    aryColours(i, j) = _
                      DecodeColorIndex(cell, True, iBlack)
                Else
                    aryColours(i, j) = _
                      DecodeColorIndex(cell, False, iWhite)
                End If

            Next cell

        Next row

    End If

    ColorIndex = aryColours

End Function

'---------------------------------------------------------------------
Private Function WhiteColorindex(oWB As Workbook)
'---------------------------------------------------------------------
Dim iPalette As Long
    WhiteColorindex = 0
    For iPalette = 1 To 56
        If oWB.Colors(iPalette) = &HFFFFFF Then
            WhiteColorindex = iPalette
            Exit Function
        End If
    Next iPalette
End Function

'---------------------------------------------------------------------
Private Function BlackColorindex(oWB As Workbook)
'---------------------------------------------------------------------
Dim iPalette As Long
    BlackColorindex = 0
    For iPalette = 1 To 56
        If oWB.Colors(iPalette) = &H0 Then
            BlackColorindex = iPalette
            Exit Function
        End If
    Next iPalette
End Function

'---------------------------------------------------------------------
Private Function DecodeColorIndex(rng As Range, _
                                  text As Boolean, _
                                  idx As Long)
'---------------------------------------------------------------------
Dim iColor As Long
    If text Then
        iColor = rng.Font.ColorIndex
    Else
        iColor = rng.Interior.ColorIndex
    End If
    If iColor < 0 Then
        iColor = idx
    End If
    DecodeColorIndex = iColor
End Function

and

=SUMPRODUCT(--(ColorIndex(A1:G100)=6),--(A1:G100="XY"))

I don't see in original post: VBA is prohibited