How do I sum up values only in cells that are color filled?

Can anyone help me further with this issue? I want to add up cells dependent
on colour - but all of the information on the links looks very complicated
and I don't know how to implement the codes. Is there a really simple guide
to doing this? usually I will have a go at anything, but this stumps me.
--
Thanks
Michele


"Ron Rosenfeld" wrote:

On Wed, 5 Jan 2005 23:11:01 -0800, "TryingExcel"
wrote:

Each cell that filled with "red" represents player A, "blue" player B, etc.
In each cell is also a score (some value). I want to total the scores for
player A (the values in all the red cells) and player B (the values in all
the "blue" cells), etc.

How does the cell get filled with a color?

If the color is there because of conditional formatting, then you will need to
use the same formula in your SUM(scores) formula.

Something like =SUMPRODUCT((Player="A")*Scores)


--ron

If the cells are manually colored and you want a count in a cell as a result
of a formula you will have to go the VBA function route.

If you just want to see a count of the blue colored cells go to EditFind

FindFormatFormatPatternBlue

Find All

With the list of "founds" in the dialog box hit CTRL + a to select all.

Now right-click on Status bar and select "Count"

For the VBA..................copy this function to a general module in your
workbook.

Function CountByColor(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Long
Dim rng As Range
Application.Volatile True
For Each rng In InRange.Cells
If OfText = True Then
CountByColor = CountByColor - _
(rng.Font.ColorIndex = WhatColorIndex)
Else
CountByColor = CountByColor - _
(rng.Interior.ColorIndex = WhatColorIndex)
End If
Next rng
End Function


If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

In a cell enter this formula =CountByColor(A1:A100,3,False)

This will count all red colored cells in the range A`1:A100

Use the number 5 to count blue cells


Gord Dibben MS Excel MVP




On Fri, 10 Apr 2009 08:45:02 -0700, Michele R
wrote:

Can anyone help me further with this issue? I want to add up cells dependent
on colour - but all of the information on the links looks very complicated
and I don't know how to implement the codes. Is there a really simple guide
to doing this? usually I will have a go at anything, but this stumps me.

Hi

thanks for that, it looks do-able! Would there be a similar simple way of
adding the values in all the blue cells and the values of the red cells?

And are there number codes for other colours, and how could I know what they
are?

Thanks for your help.

--
Thanks
Michele


"Gord Dibben" wrote:

If the cells are manually colored and you want a count in a cell as a result
of a formula you will have to go the VBA function route.

If you just want to see a count of the blue colored cells go to EditFind

FindFormatFormatPatternBlue

Find All

With the list of "founds" in the dialog box hit CTRL + a to select all.

Now right-click on Status bar and select "Count"

For the VBA..................copy this function to a general module in your
workbook.

Function CountByColor(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Long
Dim rng As Range
Application.Volatile True
For Each rng In InRange.Cells
If OfText = True Then
CountByColor = CountByColor - _
(rng.Font.ColorIndex = WhatColorIndex)
Else
CountByColor = CountByColor - _
(rng.Interior.ColorIndex = WhatColorIndex)
End If
Next rng
End Function


If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

In a cell enter this formula =CountByColor(A1:A100,3,False)

This will count all red colored cells in the range A`1:A100

Use the number 5 to count blue cells


Gord Dibben MS Excel MVP




On Fri, 10 Apr 2009 08:45:02 -0700, Michele R
wrote:

Can anyone help me further with this issue? I want to add up cells dependent
on colour - but all of the information on the links looks very complicated
and I don't know how to implement the codes. Is there a really simple guide
to doing this? usually I will have a go at anything, but this stumps me.

To Sum the values of the blue cells use this UDF.

Note: both the countbycolor and sumbycolor functions are from Chip
Pearson's site

http://www.cpearson.com/excel/topic.aspx

Function SumByColor(InRange As Range, SameColorAs As Range, _
Optional OfText As Boolean = False) As Double

Dim WhatColorIndex As Integer

If OfText = True Then
WhatColorIndex = SameColorAs(1).Font.ColorIndex
Else
WhatColorIndex = SameColorAs(1).Interior.ColorIndex
End If
If OK And IsNumeric(rng.Value) Then
SumByColor = SumByColor + rng.Value
End If
Next rng
End Function

To get a list of the index numbers for colors run this macro.

Sub ListColorIndexes()
Dim Ndx As Long
Sheets.Add
For Ndx = 1 To 56
Cells(Ndx, 1).Interior.ColorIndex = Ndx
Cells(Ndx, 2).Value = Hex(ThisWorkbook.Colors(Ndx))
Cells(Ndx, 3).Value = Ndx
Next Ndx
End Sub


On Sun, 12 Apr 2009 05:01:01 -0700, Michele R
wrote:

Hi

thanks for that, it looks do-able! Would there be a similar simple way of
adding the values in all the blue cells and the values of the red cells?

And are there number codes for other colours, and how could I know what they
are?

Thanks for your help.