How to SUM Numbers by Cell Background Color in Excel

In this tutorial you will learn how to calculate the SUM of numbers by cell background color in Microsoft Excel.

This basically involves these steps.

1. Get the VBA function code.
2. Install the function code in Excel.
3. Save the file as macro enabled.

Look in below example, the numbers are highlighted with different colors. The requirement here is to calculate the total of number for every specific color.

In the animation above, the SumByColor function is able to add the numbers based on their background color.

Installing the Function in Excel for Summing Numbers by Background Color

Since, MS Excel does not have any in built function to do this calculation, we need to create our own function. For this we need to install a VBA code in Excel.

Follow these steps to make SumByColor function.

Step1. Copy the VBA code.

Step2. Click on Developer tab of Excel, then Visual Basic.

Step3. Click on Insert tab, then module.

Step4. Paste the copied code, then close VBA window.

Step5. Use the function.

VBA Code for SUM numbers by Background Color

Copy VBA code from below.

Function SumByColor(rng As Range, color As Range) As Double
      
    Dim cell As Range
    Dim sumResult As Double
    
    Application.Volatile
    sumResult = 0
    
    For Each cell In rng
        If cell.Interior.color = color.Interior.color Then
            sumResult = sumResult + cell.Value
        End If
    Next cell
    
    SumByColor = sumResult
 
End Function

Save the File as Macro Enabled

If you need to re-use the function in future, you have to save the file as macro enabled file. For this follow these steps.

1. Click on File Tab.
2. Click on Save As.
3. In the file type, choose “Macro Enabled File”.
4. Click on Save.