Find cells with color sum other column

[yh73090]

I have two columns the first contains amounts the second column contain
cells with color.

What I need is to sum all cells in the amounts column that also have
colored cell in the second column.

I hope this makes sense.

I did do multiple searches but none addressed this quiet this way.

Thanks,
Y

 

[Frank Kabel]

Hi
see:
http://www.xldynamic.com/source/xld.ColourCounter.html
and
http://www.cpearson.com/excel/colors.htm

 

[Jason Morin]

I modified some code from MVP George Simms
(http://tinyurl.com/6go64). You can use this UDF by
pressing Alt+F11, Insert > Module, and paste in the code
below:

Function SumCellCol(rng As Range, rng2 As Range) As Double
Dim Sumcell As Range
Dim dSum As Double
Application.Volatile True
dSum = 0
For Each Sumcell In rng2
If Sumcell.Interior.ColorIndex <> -4142 Then
dSum = dSum + Sumcell.Offset(0, -1).Value
End If
Next
SumCellCol = dSum
End Function
--

You can then call it (for example) with:

=sumcellcol(A1:A4,B1:B4)

where A1:A4 contain the potential values to sum and B1:B4
are the colored cells.

HTH
Jason
Atlanta, GA

 

[yh73090]

I went to these sights but they don't sum the way I need them to sum.

Any other ideas???

I'm at work and need to provide information in a meeting.


Appreciate any help.
Yo

 

[Frank Kabel]

Hi
they should. Please describe exactly what you have tried. That is post
the formulas you have used.
This can only be done using some VBA code and both sites provide this
kind of code.
Also provide some more details about your data (column index, whcih
color to sum, etc.)

 

[yh73090]

I have a formula in the colored cells that does not apply to either of
these and it appears it is causing it not to see the colored cell.

Is this fixable?

Thank you so much, I really appreciate your time.

Yo

 

[Frank Kabel]

Hi
you really should post your formulas and describe EXACTLY what is in
each cell and what error you have. As I don't know your file your
description is not ´meaningful for me.