Sum by color using VBA in Microsoft Excel

In this article, we will create a custom function to calculate the sum of numbers in cells in the range having defined color.

For this example, sample data consists of sales team data. Sales team data contains details related to number of items sold. It contains information of related to product which is available in three colors Grey, Red and Green. We want to find the sum of item sold by product color.


To calculate the sum by color, we have created custom function “SumByColor”. This function take two range parameter as input. First range parameter define the interior color of the cell and second range parameter define the sample range.


Code explanation

Application.Volatile is used because it will recalculate every time a cell value is changed in the workbook.


Above code is used to get the interior color of the defined range.


Please follow below for the code

Option Explicit
Function SumByColor(DefinedColorRange As Range, SumRange As Range)


'Declaring variables
Dim ICol As Integer
Dim GCell As Range

'Getting the interior color of the cell
ICol = DefinedColorRange.Interior.ColorIndex

'Looping through the defined range
For Each GCell In SumRange
    If ICol = GCell.Interior.ColorIndex Then
        'Getting the sum of matching colored cell
        SumByColor = SumByColor + GCell.Value
    End If
Next GCell

End Function


If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at


Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.