use sumif function when criteria is cell hihlighted with a color highlight

[dickcarey460gmailcom]

I want to use sumif function to add a column of numbers when criteria is a fill color in the cells.  How do I do this.

[#2]

If you have a criteria in colouring the cells, use it as a criteria in your SumIfs() fn, else you'll have to create a user defined function with VBA to achieve that.

[#3]

Thanks for your reply and input. I do not have a Criteria for Cell Coloring (don't know how to do that), and I haveE no idea what VBA is or how to use t. I have used Excel for over 35 years (in Engineering, Finance, & Ocean Navigation), but I am not a Guru in it. Thanks for any additional input.
**bleep**

[#4]

@dickcarey460gmailcom 

Press Alt+F11 to activate the Visual Basic Editor.

Select Insert > Module.

Copy the following code into the module:

Function CountByColor(ARange As Range, ColorCell As Range) As Long
    Dim ACell As Range
    Application.Volatile
    For Each ACell In ARange
        If ACell.Interior.Color = ColorCell.Interior.Color Then
            CountByColor = CountByColor + 1
        End If
    Next ACell
End Function

Switch back to Excel.

Use the function like this:

In the formula =CountByColor($A$1:$A$19,D1), the first argument $A$1:$A$19 is a range with colored cells, and the second argument D1 is a cell with the color that you want to count.

Save the workbook as a macro-enabled workbook (*.xlsm), and make sure that you allow macros when you open it.

Warning: a formula like this will not automatically update its result when you change the fill color of a cell. It will be updated the next time Excel recalculates formulas; you can force this by pressing F9.

I have attached a sample workbook with the code.

[#5]

@Hans Vogelaar  How do use sumifs with cell color as part of the criteria?

[#6]

@Chuks1890 

Do you want to impose both a color condition AND other criteria? That could become complicated.

Can you provide an example of what you want to do?

[#7]

@Hans Vogelaar  See the picture below;

[#8]

@Chuks1890 

Thanks. See the attached demo workbook. The function used is

Function SumIfColour(SumRange As Range, CriteriaRange As Range, Criteria As Variant, _
        ColourRange As Range, ColourCell As Range) As Double
    Dim i As Long
    Dim r As Double
    For i = 1 To SumRange.Count
        If CriteriaRange(i).Value = Criteria And ColourRange(i).Interior.Color = _
                ColourCell.Interior.Color Then
            r = r + Val(SumRange(i).Value)
        End If
    Next i
    SumIfColour = r
End Function

[#9]

Thanks.

One more, How can i use SumifsColour(

[#10]

@Chuks1890 

Once again, please provide an example of what you want to do.