Sum by color

It's not possible to add the values in a range depending on each cells background color with the built-in SUM()-function. With the custom function below you can add the contents of cells depending on their background color:

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

The function is used in the same way as the built-in worksheetfunctions. InputRange contains the cells with the values that the function is going to add, ColorRange is a cell reference to a cell containing the background color you want restrict the adding to.
In Excel 5/95 the variables InputRange and ColorRange must be declared as Variant instead of Range. The function can easily be adapted for use with other worksheetfunctions that you want to use on cells with different background colors.

Document last updated 2002-06-26 12:44:13 Printerfriendly version

User comments:

Nestor from Santiago, Chile. wrote (2006-02-07 19:39:27 CET):

Re: List of comparative functions of excel in diferents leanguages
Hi. I found this document on the internet that shows excel functions and their equivalence in several languages. Hope it helps.

http://www.fcjs.urjc.es/finan/Macros/glosario.xls

Ole P. from Norway wrote (2005-10-15 01:53:42 CET):

Re: doesn't work with conditional formatting
This is because the conditional formatting feature actually doesn't change the background color of the cells, it just displays another background color if the proper criteria is met.
You can however change the function to check the conditional format background color settings (e.g. cl.FormatConditions(1).Interior.ColorIndex), just remember to also check if the cell value actually meets the criteria for each background color (up to 3 different settings).

Leo from Utrecht wrote (2005-10-14 16:14:30 CET):

doesn't work with conditional formatting
Hi,

When using a conditional format I noticed this isn't working.

Ole P. from Norway wrote (2004-11-14 14:57:36 CET):

Re: List of comparative functions...
You have to search the Internet to see if you can find a workbook that lists all Excel worksheet functions and translates them into the desired language.
On this page you can download a workbook that can translate individual worksheet functions between English and your local language version of Excel.

Hernan Ospina from Colombia wrote (2004-11-09 16:00:04 CET):

List of comparative functions of excel in diferents leanguages
I need a comparative file where i have all formulas in english and the traduction in other idioms like spanish.
Or all the formulas in spanish and the traduction in english.

Is that posible?

What need I?

are there any web pages to download this file?

Ole P. from Norway wrote (2004-10-27 09:09:54 CET):

Re: Recalculate after changing the color of a cell
Remove the comment mark in front of this line:
Application.Volatile
Now the formula will recalculate every time you press F9 even if no cells in the source range has changed its contents.

tjfsu from USA wrote (2004-10-26 22:35:06 CET):

Re: Recalculate after changing the color of a cell
F9 still does not recalculate for me. I am aware of the F9 function it just doesn't seem to work here. Any suggestions?

Ole P. from Norway wrote (2004-04-26 15:18:48 CET):

Re: Recalculate after changing the color of a cell
Changing the color of any cell does not make Excel calculate any formulas/functions in a worksheet. This is by design.
If you want to be sure that all formulas in the active worksheet are calculated you can press F9 (Calc Now) to perform a calculation.

mg from Minneapolis, USA wrote (2004-04-26 05:09:41 CET):

Can't get it to change result when I change color of a cell
This works great but if I later paint a cell equal to the ColorRange color the formula doesn't update. It only updates when I input/change a number in any cell referenced by the function or if I press F2 while on the cell with the function and then press Enter.

Ole P. from Norway wrote (2003-12-18 10:03:32 CET):

Re: Error
You have to paste/write the function into a normal module sheet (e.g. Module1). You can't use the module sheet belonging to the worksheet/workbook (e.g. Sheet1 or ThisWorkbook).