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

Copper Contributor

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

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.

15 Replies

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

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.

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

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**

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

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.

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

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

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

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?

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

@HansVogelaar  See the picture below;

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

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``````

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

Thanks.

One more, How can i use SumifsColour(

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

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

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

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.

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

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.

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

It works! Thank you @RLAJack

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

@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

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

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``````

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

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!