=Sum in for Color cells in Column K

[zone709]

I highlight down a sheet in Column K what i need to pay. Since the sheet is so long. It it possible to Sum the cells only highlighted in yellow thank you.

  .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0

 

[Eric W]

You can't do this with native functions, since none of the standard functions can determine the formatting of a cell. But you can do it with a UDF (User Defined Function). Open a COPY of your workbook. Right click the sheet tab on the bottom and select View Code. Press Alt-IM to Insert a Module. Paste the following code in the window that opens:

Public Function ColorSum(R As Range, cr As Range)
Dim cel As Variant, CFColor As Long, s As Double

    Application.Volatile

    s = 0
    On Error Resume Next
    For Each cel In R
        CFColor = Evaluate("Helper(" & cel.Address() & ")")
        If CFColor = cr.Interior.Color Then s = s + cel.Value
    Next cel
    
    ColorSum = s

End Function

Private Function Helper(ByVal R As Range) As Double
    Helper = R.DisplayFormat.Interior.Color
End Function

Go back to Excel and use the function like this:

Book1
	A	B	C	D
1	Code	Sum		Amount
2	Rent	17		1
3	Food	13		2
4	Gas	18		3
5				4
6				5
7				6
8				7
9				8
10				oops
11				10
12				11
13
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=colorsum($D$2:$D$20,A2)

The first parameter of the function is the range to search, the second is a cell with the color you want to sum. Also note that Excel doesn't force a recalculation when a cell is formatted. So if you format a cell red, the total won't change until you press F9 or update a cell.

 

[zone709]

thanks for the reply. I haven't gotten to it yet but i will let you know.