Table of Contents
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.
