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