Sum Cells by Color in Excel using VBA – ExcelVbaAutomation

Introduction/Problem Statement

In this blog, we will talk about how to Sum Cells by Color the value of all cells of a particular range that are a particular color. There is no SUMIF function in excel to sum based on color.

Solution

This code will Sum Cells by Color the value of the range A2 to A28 to if the cell background color is yellow and paste sum value to range G5.

Sub KDataScience_SumByColor()
    
    Dim lrow As Long
    Dim Iloop As Long
    Dim Colour As String
    Dim Number As Long
    Number = 0
    
    'To find the color index of criteria
    Colour = Sheet1.Range("F5").Interior.ColorIndex
    'To find the last row number of range
    lrow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    
    For Iloop = 2 To lrow
        'Check colorIndex of range match with ctireria
        If Worksheets("Sheet1").Range("A" & Iloop).Interior.ColorIndex = Colour Then
            Number = Number + Worksheets("Sheet1").Range("A" & Iloop).Value
          Else
         End If
     Next Iloop
     'Assign sum value to range G5 to show on sheet
    Sheet1.Range("G5").Value = Number
    
End Sub

Function to Sum Cells by Color

There is no SUMIF function in excel to sum based on color. If a user wants to sum based on color, then we must create a function. I have created a function for this you can simply paste this code in a module in Visual basic editor and save the workbook in “XLSM” or “XLSB” format.

To use this function type =SumBycolor(A1:A10,B4) in cell – where A1:A10 is the sum range and B4 is the cell reference that contains the background color that is our criteria.

Function SumByColor(rng As Range, ctr As Range)
    
    Dim cell As Range
    Dim ctr_color As Integer
    'To find the color index number of criteria
    ctr_color = ctr.Interior.ColorIndex
    
    For Each cell In rng
        'To check if cell value is equal to criteria color index
        If cell.Interior.ColorIndex = ctr_color Then
            SumByColor = SumByColor + cell.Value
        End If
     Next cell
    
End Function

Conclusion

This blog is very important for MIS reporting because in many cases users need to Sum Cells by Color of the range which contains a specific color. We have created a function above which you can use directly only after pasting code in the module of the VBA code window.