Feb 27 2023 07:46 PM
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.
Feb 27 2023 08:42 PM
Mar 05 2023 11:36 AM
Mar 05 2023 12:26 PM
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.
Jul 13 2023 03:47 AM
@Hans Vogelaar How do use sumifs with cell color as part of the criteria?
Jul 13 2023 04:08 AM
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?
Jul 17 2023 03:36 AM
Jul 17 2023 03:59 AM
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
Jul 17 2023 04:35 AM
Jul 17 2023 05:00 AM
Once again, please provide an example of what you want to do.