How to Sum Using Colors in Excel
By
SpreadCheaters
Excel is a powerful tool for managing data, and it comes with a variety of features to help you organize and analyze your data efficiently. One such feature is the ability to sum cells by color. This can be a helpful tool when working with large datasets, as it can help you quickly calculate totals for specific groups of data. Here we have a dataset which contains student names and their marks out of 100. In this tutorial we will be adding the marks of students who have passed and failed separately using color.
Step 1 – Color code your data.
– For color coding, change the color of the cell according to your requirements.
– For coloring the cells go to the Home tab in the font group and click on Fill Color drop down arrow and choose the color.
– We have used two colors Green for Pass (Marks > 50) and Red for Fail (Marks < 50).
Step 2 – Type VBA code.
– We are using VBA to create our own User-Defined Function to perform addition using colors.
– The function we created is called “SumByColor”, it will be used in the cell to find the sum using color.
– To open VBA you have to first enable Developer Tab in the ribbon by following steps:
File > Options > Customize Ribbon > Main Tabs > Check Developer option.
– On the Ribbon open Developer tab and open Visual Basic.
– To start coding click on Insert Tab in VBA and click Add Module.
– Type in the code.
– Code explanation is given in the comments in the picture below.
– VBA code is also given at the end of the article.
Step 3 – Type the Formula.
– The function we created has the following syntax:
=SumByColor( Cell address to match the color with, Range of cells you want to sum )
– In our case Formula will be:
=SumByColor(D2,B2:B11)
Step 4 – Find values for the rest of the Values.
– Drag the cell with the formula from bottom right to the rest of the cells.
– Excel will produce the results automatically.
VBA code used in step 4:
| Function SumByColor(CellColor As Range, rRange As Range) Dim cSum As Long Dim ColIndex As Integer ‘ used to store the color index of the cell specified by “CellColor”. ColIndex = CellColor.Interior.ColorIndex For Each cl In rRange If cl.Interior.ColorIndex = ColIndex Then cSum = WorksheetFunction.Sum(cl, cSum) End If Next cl SumByColor = cSum ‘Return value of cSum is stored in “SumByColor” End Function |
