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

[TryingExcel]

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.

 

[JulieD]

Hi

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

Cheers
JulieD

 

[Bob Phillips]

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)

 

[Ron Rosenfeld]

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

 

[Michele R]

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.

 

[Gord Dibben]

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

Find>Format>Format>Pattern>Blue

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 Insert>Module. 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

 

[Michele R]

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.

 

[Gord Dibben]

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