Table of Contents
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.
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
'Assign sum value to range G5 to show on sheet
Sheet1.Range("G5").Value = Number
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
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.