can I add values of cells that are certain colours?
 

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?'

can I add values of cells that are certain colours?
 

If you're using XL 2007 this is possible without customization. If you're
using an earlier version of Excel you can do this with macros. See:
http://www.cpearson.com/excel/colors.htm for more info.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"GCC" wrote:

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?'

can I add values of cells that are certain colours?
 

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.

"Dave F" wrote:

If you're using XL 2007 this is possible without customization. If you're
using an earlier version of Excel you can do this with macros. See:
http://www.cpearson.com/excel/colors.htm for more info.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"GCC" wrote:

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?'

can I add values of cells that are certain colours?
 

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

--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"GCC" wrote:

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.

"Dave F" wrote:

If you're using XL 2007 this is possible without customization. If you're
using an earlier version of Excel you can do this with macros. See:
http://www.cpearson.com/excel/colors.htm for more info.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"GCC" wrote:

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?'

can I add values of cells that are certain colours?
 

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 InsertModule.

Paste the text above into that module.

FileSave 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



On Tue, 10 Apr 2007 07:56:04 -0700, GCC wrote:

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.

"Dave F" wrote:

If you're using XL 2007 this is possible without customization. If you're
using an earlier version of Excel you can do this with macros. See:
http://www.cpearson.com/excel/colors.htm for more info.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"GCC" wrote:

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?'

can I add values of cells that are certain colours?
 

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

Thanks again.



"Gord Dibben" wrote:

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 InsertModule.

Paste the text above into that module.

FileSave 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



On Tue, 10 Apr 2007 07:56:04 -0700, GCC wrote:

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.

"Dave F" wrote:

If you're using XL 2007 this is possible without customization. If you're
using an earlier version of Excel you can do this with macros. See:
http://www.cpearson.com/excel/colors.htm for more info.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"GCC" wrote:

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?'