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

[twaccess]

Is there a way I can use a formula like COUNTIF to scan a large spreadsheet to return the number of occurrences of a value say 'XY' where the cell colour is also highlighted in Yellow ? At the same time, ignoring all the occurences where the cell isn't highlighted ?

Any help would be gratefully received.

Tdub

[MarvinP]

Hi twaccess,

Counting values in cells is easy but the color something is, is very hard. If you had the formula that colors these cells yellow and add that criteria to a CountIfS function you might succeed.

[AliGW]

Hi twaccess,

Counting values in cells is easy but the color something is, is very hard. If you had the formula that colors these cells yellow and add that criteria to a CountIfS function you might succeed.

It's not hard at all if the colour was applied using conditional formatting, as in such cases, the CF formula can be incorporated into the counting formula. If it has been applied manually, then it's harder.

[Pepe Le Mokko]

It's not hard at all if the colour was applied using conditional formatting, as in such cases, the CF formula can be incorporated into the counting formula.

Isn't that what Marvin implied?

Hi twaccess,

If you had the formula that colors these cells yellow and add that criteria to a CountIfS function you might succeed.

[AliGW]

Isn't that what Marvin implied?

Implied, yes, but didn't actually say. I was providing clarity in case the OP was unable to infer what Marvin was implying.

[D.Lovell]

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'.

[Khalidngo]

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.

[sandy666]

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