SumByColor is not working

Posted on 2009-07-09
Last Modified: 2012-08-14
I am using this formula to sum the values of the tan cells in the identified range but all I get for the value is #NAME?

=SumByColor(B2:B34, 40)
Question by:SpyderGST1

Accepted Solution

bboswell earned 250 total points
Comment Utility
I don't think there is a function in Excel called SumByColor. There is how ever a function that I located at (

You would need this in the Macro section first for this function to work.
Function SumByColor(InputRange As Range, ColorRange As Range) As Double

' returns the sum of each cell in the range InputRange that has the same

' background color as the cell in ColorRange

' example: =SumByColor($A$1:$A$20,B1)

' range A1:A20 is the range you want to sum

' range B1 is a cell with the background color you want to sum

Dim cl As Range, TempSum As Double, ColorIndex As Integer

    ' Application.Volatile ' this is optional

    ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex

    TempSum = 0

    On Error Resume Next ' ignore cells without values

    For Each cl In InputRange.Cells

        If cl.Interior.ColorIndex = ColorIndex Then 

            TempSum = TempSum + cl.Value

        End If

    Next cl

    On Error GoTo 0

    Set cl = Nothing

    SumByColor = TempSum

End Function

Open in new window

LVL 12

Expert Comment

Comment Utility
Hi SpyderGST1,
This is a UDF created by Chip Pearson.  It will sum values by interior color (among other things).

Let me know if you have questions on how to implement.

Function SumColor(TestRange As Range, SumRange As Range, _

    ColorIndex As Long, Optional OfText As Boolean = False) As Variant


' SumColor

' This function returns the sum of the values in SumRange where

' the corresponding cell in TestRange has a ColorIndex (of the

' Font is OfText is True, or of the Interior is OfText is omitted

' or False) equal to the specified ColorIndex. TestRange and

' SumRange may refer to the same range. An xlErrRef (#REF) error

' is returned if either TestRange or SumRange has more than one

' area or if TestRange and SumRange have differing number of

' either rows or columns. An xlErrValue (#VALUE) error is

' returned if ColorIndex is not a valid ColorIndex value.

' If ColorIndex is 0, xlColorIndexNone is used if OfText is

' False or xlColorIndexAutomatic if OfText is True. This allows

' the caller to specify 0 for no color applied.


Dim D As Double

Dim N As Long

Dim CI As Long

Application.Volatile True

If (TestRange.Areas.Count > 1) Or _

    (SumRange.Areas.Count > 1) Or _

    (TestRange.Rows.Count <> SumRange.Rows.Count) Or _

    (TestRange.Columns.Count <> SumRange.Columns.Count) Then

    SumColor = CVErr(xlErrRef)

    Exit Function

End If


If ColorIndex = 0 Then

    If OfText = False Then

        CI = xlColorIndexNone


        CI = xlColorIndexAutomatic

    End If


    CI = ColorIndex

End If

Select Case CI

    Case 0, xlColorIndexAutomatic, xlColorIndexNone

        ' ok

    Case Else

        If IsValidColorIndex(ColorIndex:=ColorIndex) = False Then

            SumColor = CVErr(xlErrValue)

            Exit Function

        End If

End Select

For N = 1 To TestRange.Cells.Count

    With TestRange.Cells(N)

    If OfText = True Then

        If .Font.ColorIndex = CI Then

            If IsNumeric(.Value) = True Then

                D = D + .Value

            End If

        End If


        If .Interior.ColorIndex = CI Then

            If IsNumeric(.Value) = True Then

                D = D + .Value

            End If

        End If

    End If

    End With

Next N


SumColor = D

End Function

Open in new window

LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Hello SpyderGST1,

The error message suggests you have the function in the wrong place .. it should reside in a normal code module i.e. NOT a class, worksheet or workbook code module


Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel and Formulas 8 30
Excel 6 16
VBA in SharePoint 3 16
TT Status Chang 3 29
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now