How to sum data based on font or cell color?

As subject, data are in different color font or cell. Can I sum the data by different color? e.g. add all the data in red font and/or in blue cell.
Many thanks.

Hi

take a look at Bob Phillip's site for a solution
http://xldynamic.com/source/xld.ColourCounter.html

--
Regards

Roger Govier


"techiesol" <[email protected]>
wrote in message
news:[email protected]...
>
> As subject, data are in different color font or cell. Can I sum the
> data
> by different color? e.g. add all the data in red font and/or in blue
> cell.
> Many thanks.
>
>
> --
> techiesol
> ------------------------------------------------------------------------
> techiesol's Profile:
> http://www.excelforum.com/member.php...o&userid=34728
> View this thread:
> http://www.excelforum.com/showthread...hreadid=544924
>

"Roger Govier" wrote:
> take a look at Bob Phillip's site for a solution
> http://xldynamic.com/source/xld.ColourCounter.html

Just to add on a little to Roger's suggestion ..

Here's a sample file (~ "starter's kit") which
contains an implementation of Bob Phillips' ColorIndex
Function from his "Processing Coloured Cells" page (link above)
and some examples on how to use the UDF in Excel
(kit was given in response to a previous query)

Link to the sample file (construct details inside):
http://www.savefile.com/files/3232462
CountSumCellsByColor_Using_BobPhillips_ColorIndex_UDF.xls

Note that as the UDF is non volatile, recalc has to be
forced, even if calc mode is set to Automatic. Press
F9 to force recalc. Read the section "Constraints" in
Bob's page.

The sample should help you get started ..

(not sure about font color, though ..)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> .. My problem is that even though the
> conventional formatting is working for the cells in the row, when you
> click on each individual cell, the shading shows no color (even though
> you are looking at it and it is GREEN). Because of this, the UDF is not
> calculating right.

The above sounds like the cells are conditionally formatted, which Bob's UDF
doesn't cover, re his "Contraints" section in the page:
"... The second shortcoming is that this technique at present does not cater
for cells that are coloured due to conditional formatting."

For CF cases, one way is to try using the same conditions as applied for the
CF
Eg if col B is conditionally formatted with the formula: =A1>10,
then we could use something like this in say, C1: =SUMIF(A:A,">10",B:B)
to sum col B if col A >10
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jzzman" wrote:

>
> Thanks for the posting. Funny, I am working on the same thing. I have a
> small problem with this. I am using conventional formatting to change
> the color of a row based on the contents of a cell. Status column
> "completed" = change row shade to green. Now, I am using the UDF below
> to sum the green shaded rows. My problem is that even though the
> conventional formatting is working for the cells in the row, when you
> click on each individual cell, the shading shows no color (even though
> you are looking at it and it is GREEN). Because of this, the UDF is not
> calculating right. Any thoughts here? I greatly appreciate it.
>
> Jzzman

Max wrote...
....
>For CF cases, one way is to try using the same conditions as applied for the CF
>Eg if col B is conditionally formatted with the formula: =A1>10,
>then we could use something like this in say, C1: =SUMIF(A:A,">10",B:B)
>to sum col B if col A >10
....

But if the conditional formatting formulas or conditions are unknown,
it's still possible to sum by color index using VBA. The following sums
by cell background color index, aka, interior color index.


Function foo(rng As Range, ci As Variant) As Double
Dim fc As FormatCondition, c As Range
Dim fcf1 As Variant, cv As Variant

For Each c In rng
cv = c.Value2

If VarType(cv) = vbDouble Then

If c.Interior.ColorIndex = ci Then
foo = foo + cv

Else
For Each fc In c.FormatConditions
fcf1 = Application.ConvertFormula( _
Application.ConvertFormula( _
fc.Formula1, xlA1, xlR1C1, , ActiveCell), _
xlR1C1, xlA1, xlAbsolute, c)

If Left(fcf1, 1) <> "=" Then fcf1 = CDbl(fcf1)

If fc.Type = xlExpression Then

If CBool(Evaluate(fcf1)) Then
If fc.Interior.ColorIndex = ci Then foo = foo + cv
Exit For
End If

ElseIf (fc.Operator = xlEqual And cv = fcf1) _
Or (fc.Operator = xlNotEqual And cv <> fcf1) _
Or (fc.Operator = xlLess And cv < fcf1) _
Or (fc.Operator = xlLessEqual And cv <= fcf1) _
Or (fc.Operator = xlGreater And cv > fcf1) _
Or (fc.Operator = xlGreaterEqual And cv >= fcf1) _
Or (fc.Operator = xlBetween And fcf1 <= cv _
And cv <= CDbl(fc.Formula2)) _
Or (fc.Operator = xlNotBetween And (cv < fcf1 _
Or CDbl(fc.Formula2) < cv)) Then
If fc.Interior.ColorIndex = ci Then foo = foo + cv
Exit For

End If

Next fc

End If

End If

Next c

End Function