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

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

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

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

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

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

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