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]

@HansVogelaar  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]

@HansVogelaar  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.

[#11]

@HansVogelaar 

I was wondering how to do the same thing e.g. sum the numbers in column D which are in rows with blue highlighted cells. Or even better sum all the numbers in blue highlighted cells

I tried your formula but may have chosen the wrong cells.

[#12]

@RLAJack 

For summing numbers in cells with a specific fill color:

Function SumByColor(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
            SumByColor = SumByColor + Val(ACell.Value)
        End If
    Next ACell
End Function

To sum the values of cells in F4:F8 with the same fill color as C4, use

=SumByColor(F4:F8, C4)

See the attached workbook.

[#13]

It works! Thank you @RLAJack 

[#14]

@HansVogelaar

Thanks for uploading this! Better than adding it all on your own. However, when I use it, it seems to omit change. For example, I'm creating a budget and have categories of expenses color coded. When I use this formula, it produces a whole number instead of the exact amount of change if added properly. The values that should be included in gas for example are: $16.87, $23.71, $75.23, $50.01 & $17.31 = $183.13

[#15]

@jeremyc740 

The earlier questions in this discussion were about summing whole numbers. If you want to sum numbers with decimals, change the return type of the function from Long to Double:

Function SumByColor(ARange As Range, ColorCell As Range) As Double
    Dim ACell As Range
    Application.Volatile
    For Each ACell In ARange
        If ACell.Interior.Color = ColorCell.Interior.Color Then
            SumByColor = SumByColor + Val(ACell.Value)
        End If
    Next ACell
End Function

[#16]

That would be it! I've had very little exposure to creating macros and typing code, so I knew it had to be something with that when my formatting within the sheet was accurate. Greatly appreciated @HansVogelaar!