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

Hi

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

--
Regards

Roger Govier


"techiesol"
wrote in message
...

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

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:
"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
---


--
jzzman


------------------------------------------------------------------------
jzzman's Profile: http://www.excelforum.com/member.php...o&userid=24838
View this thread: http://www.excelforum.com/showthread...hreadid=544924

.. 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: =A110,
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: =A110,
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

Thanks everyone. I'll try them both.

jzzman


--
jzzman


------------------------------------------------------------------------
jzzman's Profile: http://www.excelforum.com/member.php...o&userid=24838
View this thread: http://www.excelforum.com/showthread...hreadid=544924