can I add values of cells that are certain colours?

G

GCC

I was wondering if it was possible to write a formula in excel that says for
example 'add the numbers in all cells that are coloured purple?'
 
G

GCC

I'm sorry I am quite a beginner when it comes to things like this. I have
looked at the code on the website you suggested but I don't understand how to
get this into excel? Any help you can give would be greatly appriciated.

Thanks.
 
D

Dave F

Fair question. The code shown on this page are UDFs--user defined
functions--which are custom-designed Excel functions, which functions
eliminate/mitigate certain limitations in Excel, such as summing by cell
color. Once such a UDF is created, using it just requires that you use the
function like any other function.

To put a function into one of your workbooks, do the following:

1) Right click on a tab in the workbook and select View Code
2) This opens up the Visual Basic Editor (VBE)
3) Right click on the file name in the left hand pane and select "Insert
Module"
4) Copy the relevant code from the link I give and paste it into the module
window (the big pane to the right of the where you found the file name)
5) Hit the save icon in the toolbar or File--Save
6) Go back to the Excel worksheet and enter the function and use it.

Example: if I wanted to use the function CellColorIndex, after doing the 6
steps above, I would enter in the spreadsheet =CellColorIndex(A1) and,
assuming A1 was shaded, the function would return the numerical value
corresponding to the color used in A1.

Post back with any questions.

Dave
 
G

Gord Dibben

To add the SumByColor Function to your workbook.

Copy this text between the ................................

Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Double
'
' This function return the SUM of the values of cells in
' InRange with a background color, or if OfText is True a
' font color, equal to WhatColorIndex.
'
Dim Rng As Range
Dim OK As Boolean

Application.Volatile True
For Each Rng In InRange.Cells
If OfText = True Then
OK = (Rng.Font.ColorIndex = WhatColorIndex)
Else
OK = (Rng.Interior.ColorIndex = WhatColorIndex)
End If
If OK And IsNumeric(Rng.Value) Then
SumByColor = SumByColor + Rng.Value
End If
Next Rng

End Function

........................................................

Now, with your workbook open hit Alt + F11 to open the Visual Basic Editor.

CTRL + r to open project explorer.

Right-click on your workbook/project and Insert>Module.

Paste the text above into that module.

File>Save then hit Alt + q to return to Excel.

You can call this function from a worksheet cell with a formula like
=SUMBYCOLOR(A1:A10,3,FALSE) where 3 is the colorindex number(red in this
case)

If you don't care about font color just use =SumByColor(A1:A10,3)

To get a list of Excel's default colorindex numbers see David McRitchie's site.

http://www.mvps.org/dmcritchie/excel/colors.htm


Gord Dibben MS Excel MVP
 
G

GCC

Just wanted to say thanks to Dave and Gods for your help! I have managed to
do this now.

Thanks again.
 
Top