Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing cells by background colour
I have a column of data (E5:E16) and different cells in that column have a different background colour. I would like to sum the data in the cells depending on the background colour e.g Cell E17 sums every cell with a red background in (E5:E16) Cell E18 sums every cell with yellow background in (E5:E16) How can I do this? Thanks for any help -- frustrated ------------------------------------------------------------------------ frustrated's Profile: http://www.excelforum.com/member.php...o&userid=27778 View this thread: http://www.excelforum.com/showthread...hreadid=493858 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing cells by background colour
frustrated, have a look here for a way to do it
http://www.cpearson.com/excel/SortByColor.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "frustrated" wrote in message ... I have a column of data (E5:E16) and different cells in that column have a different background colour. I would like to sum the data in the cells depending on the background colour e.g Cell E17 sums every cell with a red background in (E5:E16) Cell E18 sums every cell with yellow background in (E5:E16) How can I do this? Thanks for any help -- frustrated ------------------------------------------------------------------------ frustrated's Profile: http://www.excelforum.com/member.php...o&userid=27778 View this thread: http://www.excelforum.com/showthread...hreadid=493858 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing cells by background colour
Hi Frustrated,
See Chip Pearson at: http://www.cpearson.com/excel/colors.htm --- Regards, Norman "frustrated" wrote in message ... I have a column of data (E5:E16) and different cells in that column have a different background colour. I would like to sum the data in the cells depending on the background colour e.g Cell E17 sums every cell with a red background in (E5:E16) Cell E18 sums every cell with yellow background in (E5:E16) How can I do this? Thanks for any help -- frustrated ------------------------------------------------------------------------ frustrated's Profile: http://www.excelforum.com/member.php...o&userid=27778 View this thread: http://www.excelforum.com/showthread...hreadid=493858 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing cells by background colour
http://www.xldynamic.com/source/xld.ColourCounter.html
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "frustrated" wrote in message ... I have a column of data (E5:E16) and different cells in that column have a different background colour. I would like to sum the data in the cells depending on the background colour e.g Cell E17 sums every cell with a red background in (E5:E16) Cell E18 sums every cell with yellow background in (E5:E16) How can I do this? Thanks for any help -- frustrated ------------------------------------------------------------------------ frustrated's Profile: http://www.excelforum.com/member.php...o&userid=27778 View this thread: http://www.excelforum.com/showthread...hreadid=493858 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing cells by background colour
Give this a look...
http://www.cpearson.com/excel/colors.htm -- HTH... Jim Thomlinson "frustrated" wrote: I have a column of data (E5:E16) and different cells in that column have a different background colour. I would like to sum the data in the cells depending on the background colour e.g Cell E17 sums every cell with a red background in (E5:E16) Cell E18 sums every cell with yellow background in (E5:E16) How can I do this? Thanks for any help -- frustrated ------------------------------------------------------------------------ frustrated's Profile: http://www.excelforum.com/member.php...o&userid=27778 View this thread: http://www.excelforum.com/showthread...hreadid=493858 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing cells by background colour
Thanks for all the replies. I have had a look at the sites recommended unfortunately I dont reall understand programming but I have given it a go. I have used th following code: Summing The Values Of Cells With A Specific Color The following function will return the sum of cells in a range tha have either an Interior (background) or Font of a specified color. InRange is the range of cells to examine, WhatColorIndex is th ColorIndex value to count, and OfText indicates whether to return th ColorIndex of the Font (if True) or the Interior (if False). 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 You can call this function from a worksheet cell with a formula like =SUMBYCOLOR(A1:A10,3,FALSE) Now this works but this isn't what I want to do as the final value jus tells me how many cells I have with a red background whereas I want t sum all the numbers in a cell with a red background. I have triesd other things off these sites but the above is the onl one I can get to work. Can someone point me in the right direction? Thank -- frustrate ----------------------------------------------------------------------- frustrated's Profile: http://www.excelforum.com/member.php...fo&userid=2777 View this thread: http://www.excelforum.com/showthread.php?threadid=49385 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing cells by background colour
The link on the page that I gave you has that very same example.
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "frustrated" wrote in message ... Thanks for all the replies. I have had a look at the sites recommended unfortunately I dont really understand programming but I have given it a go. I have used the following code: Summing The Values Of Cells With A Specific Color The following function will return the sum of cells in a range that have either an Interior (background) or Font of a specified color. InRange is the range of cells to examine, WhatColorIndex is the ColorIndex value to count, and OfText indicates whether to return the ColorIndex of the Font (if True) or the Interior (if False). 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 You can call this function from a worksheet cell with a formula like =SUMBYCOLOR(A1:A10,3,FALSE) Now this works but this isn't what I want to do as the final value just tells me how many cells I have with a red background whereas I want to sum all the numbers in a cell with a red background. I have triesd other things off these sites but the above is the only one I can get to work. Can someone point me in the right direction? Thanks -- frustrated ------------------------------------------------------------------------ frustrated's Profile: http://www.excelforum.com/member.php...o&userid=27778 View this thread: http://www.excelforum.com/showthread...hreadid=493858 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change a cell background colour to my own RGB colour requirements | Excel Discussion (Misc queries) | |||
Summing a range depending on cell background colour | Excel Programming | |||
Summing a range if a certain background colour | Excel Programming | |||
Counting cells with a specific background colour | Excel Discussion (Misc queries) | |||
Counting/summing cells based on background color | Excel Programming |