Sum By Color in Excel 2013

To sum by color in Excel 2013 (and previous versions) you must create a VBA Module. Don’t worry, it sounds scarier than it is. Follow these steps to create a new function called SumByColor to your workbook.

  1. Press ALT+F11 to open Visual Basic for Applications
  2. Click Insert | Module
  3. Paste in the following code:

    Function SumByColor(MyRange As Range, MyColor As Range)

    SumByColor = 0

    TheColor = MyColor.Interior.Color

    For Each cell In MyRange

    If cell.Interior.Color = TheColor Then

    SumByColor = SumByColor + cell.Value

    End If

    Next cell

    End Function

  4. Press CTRL+S to save your workbook.
  5. Close Microsoft Visual Basic for Applications
  6. Use your new function in your workbook using this syntax:
    =SumByColor(Range,Color)
    • Range is the Range of cells you want Summed.
    • Color is a cell that contains the color you want summed.
  7. Example:
  8. To preserve this functionality in the workbook, you will need to save it as an “Excel Macro-Enabled Workbook
Advertisements
This entry was posted in Computers and Internet. Bookmark the permalink.

2 Responses to Sum By Color in Excel 2013

  1. phil says:

    helpful but it is rounding to the nearest whole number and i need it to be exact.

    • Josh Martell says:

      Check the formatting of the cell you have the formula in. This function doesn’t specify rounding. I tried it with data containing 6 characters after the decimal point and it summed the values accurately. Best of luck, Josh.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s