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

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.

Hi

check out Chip Pearson's notes on this at
http://www.cpearson.com/excel/colors.htm

Cheers
JulieD

"TryingExcel" wrote in message
...
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.

There is a solution at
http://www.xldynamic.com/source/xld.ColourCounter.html, but note 2 things

Firstly, if there is a worksheet change that triggers a recalculation, the
colour count does not update. Secondly, if you change a colour of one of the
cells, the count does not update, as a colour change does not trigger a
recalculation.

The former can be changed by adding 'Application Volatile' to the code. The
second is trickier, so I tend to use a button to set a cell's colour, and
have that button code force a recalculation.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"TryingExcel" wrote in message
...
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.

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

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

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

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.

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

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.

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

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.