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.
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) Application.Volatile '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
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 firstname.lastname@example.org
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.