sumif or countif based on font color

[Kok Yong Lee]

Hi there,

I would like to sum the number if a range of cells if only the font color is
match up. e.g. sum up all value in A1:A15 only when the font is in red
color.

Currently I am doing it in VBA with a if loop checking against the font
property. Just wonder can this be done with a much simpler formulat in
worksheet cell?

thanks in advance.

 

[Frank Kabel]

Hi
you'll need VBA for this. Below a repost
------
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

 

[Chip Pearson]

A VBA function is the only way to do this. See the SumByColor
function at www.cpearson.com/excel/colors.htm .


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Jerry W. Lewis]

As was noted by other respondents, VBA must be involved. You could
write a UDF to check the color that could be called in a cell formula
http://groups.google.com/groups?threadm=3F6073FD.1000108@no_e-mail.com
gives a similar example.

The main problem with the UDF in cell formula approach, is that
reformatting cells triggers no event, so you must manually force the
recalc, even if the function is declared volatile.

Jerry

 

[onefastcobra_99]

How do you force the reaload

 

[Jerry W. Lewis]

Ctrl+Alt+F9 triggers a full recalc. Documented in Help for "Keyboard
shortcuts", subtopic "Enter and calculate formulas"

Jerry