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.

ArrowRaw

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.

ArrowOutput

Code explanation

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

DefinedColorRange.Interior.ColorIndex

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

 

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 info@exceltip.com

Users are saying about us...

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube