counting cells in a range w/two attributes

[lh]

Is there a way to use the countif function to count the
cells in a range that 1) have a certain background color
and 2) that contain a specific character for example "A".
Something like this I think?

=COUNTIF($E112:$K163,Font.Color($P$150))

Thanks in advance, lh

 

[Frank Kabel]

Hi
you'll need VBA for this. see:
http://www.cpearson.com/excel/colors.htm

 

[JulieD]

Hi

you need to use some code to do this .. here's a copy of a post by Frank
Kabel who refers to code written by Harlan Grove & Bob Phillips:

using a procedure from Bob Phillips and Harlan Grove you may try the
following:

=SUMPRODUCT(--(ColorIndex(A1:A100)=3))

to count all red cells (background color) within the range A1:A100

or

=SUMPRODUCT(--(ColorIndex(A1:A100,TRUE)=3))

to count all red cells (font color) within the range A1:A100

To get the colorindex of a specific cell use

=ColorIndex(A1)

Adapt this to your requirements

********************

'Code to paste in one of your modules


'---------------------------------------------------------------------
Function ColorIndex(rng As Range, _
Optional text As Boolean = False) As Variant

'---------------------------------------------------------------------
' Function: Returns the colorindex of the supplied range
' Synopsis:
' Author: Bob Phillips/Harlan Grove
'
'---------------------------------------------------------------------
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

*****************

so using the above information and the following formula

=SUMPRODUCT((ColorIndex(A1:A14)=3)*(A1:A14="A"))

you can count the number of cells with a background colour of red &
containing the letter A

Cheers

JulieD