sumif or countif based on font color
 

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.

sumif or countif based on font color
 

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



--
Regards
Frank Kabel
Frankfurt, Germany


Kok Yong Lee wrote:
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.

sumif or countif based on font color
 

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


"Kok Yong Lee" wrote in message
...
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.

sumif or countif based on font color
 

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?thre...0no_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

Kok Yong Lee wrote:

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.

sumif or countif based on font color
 

How do you force the reaload

--
Message posted from http://www.ExcelForum.com

sumif or countif based on font color
 

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

Jerry

onefastcobra_99 < wrote:

How do you force the reaload?