help with criteria and sum

[akantrow2]

Hi -
I would like to know how to make certain cells one color, other cells
another color, then sum those cells based on their color. Could someone
assist me on how to do that?
I'm using office 2007.
thank you!

 

[Jacob Skaria]

You will not be able to do that with worksheet functions; will have to
develop a macro which does this using the color index.

If this post helps click Yes

 

[akantrow2]

thank you, Jacob....do you know where on this discussion group i could find
additional information on how to do that? i'm a novice at Excel. Thanks so
much!

 

[Jacob Skaria]

Try the below UDF. If you are new to macros set the Security level to
low/medium in (Tools|Macro|Security). From workbook launch VBE using
short-key Alt+F11. From menu 'Insert' a module and paste the below
function.Get back to Workbook.

Function GetSumbyColor(varRange As Range, Optional varIDX As Long)
If varIDX = 0 Then varIDX = -4142
For Each cell In varRange
If cell.Interior.ColorIndex = varIDX Then
GetSumbyColor = GetSumbyColor + cell.Value
End If
Next
End Function

Enter the below formula in any cell ;; A1:20 being the range with colored
cells. The below user defined function will give you the sum for cells that
are colored RED.

in cell B1
to return sum of all RED colored cells
=getsumbycolor(A1:A20,3)

to return sum of all cells which are not colored
=getsumbycolor(A1:A5,0)
OR
=getsumbycolor(A1:A5)


Use the color index for more colors
1 = black
2 = white
3 = red
4 = green
5 = blue
6 = yellow

and so on
If this post helps click Yes

 

[Shane Devenshire]

Hi,

Don't give up quite yet...

Please give us more details, for example do you want to color the cells
based on their value and if so what are your condtions. Or are you coloring
cell at random based on nothing.

I think you can do what you want if you color coding is not random, but we
need to understand your rules.