A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

sum the color cells



 
 
Thread Tools Display Modes
  #1  
Old May 24th, 2010, 07:59 AM posted to microsoft.public.excel.worksheet.functions
reza
external usenet poster
 
Posts: 122
Default sum the color cells

hi all.

i have data with different color.
i one column i have 3 color, blue, yellow and red.
now i want to count & sum based on the colors.
how to create formula for that?

many thanks...

reza
  #2  
Old May 24th, 2010, 08:22 AM posted to microsoft.public.excel.worksheet.functions
FSt1
external usenet poster
 
Posts: 2,788
Default sum the color cells

hi,
you have xl07 or higher, you are suppose to have this capability already.
see help. i am having research trouble confirming that. if you have xl03 or
earlier, see this site....
http://cpearson.com/excel/colors.aspx

regards
FSt1

"reza" wrote:

hi all.

i have data with different color.
i one column i have 3 color, blue, yellow and red.
now i want to count & sum based on the colors.
how to create formula for that?

many thanks...

reza

  #3  
Old May 24th, 2010, 08:34 AM posted to microsoft.public.excel.worksheet.functions
reza
external usenet poster
 
Posts: 122
Default sum the color cells

HI FSt1...

sorry, i use 2007....
is that link compatible with 07?

thanks

"FSt1" wrote:

hi,
you have xl07 or higher, you are suppose to have this capability already.
see help. i am having research trouble confirming that. if you have xl03 or
earlier, see this site....
http://cpearson.com/excel/colors.aspx

regards
FSt1

"reza" wrote:

hi all.

i have data with different color.
i one column i have 3 color, blue, yellow and red.
now i want to count & sum based on the colors.
how to create formula for that?

many thanks...

reza

  #4  
Old May 24th, 2010, 09:12 AM posted to microsoft.public.excel.worksheet.functions
L. Howard Kittle
external usenet poster
 
Posts: 516
Default sum the color cells

Give this a try. Copy and right click on the sheet tab and paste in the
sheet module. Provides a summation list in F10, F11 & F12 along with a
Message Bos of the results.

Probably want to try on a test worksheet first.

Sums the values of all Blue, Red and Yellow formatted cells in A1:A1000.

Will not work on conditional formatted cells.

If you are trying to sum the colors of the FONTS of each cell in A:A then
post back. That is quite possible also.

Sub SumColorCount()
Dim Blue5 As Integer, _
Red3 As Integer, _
Yellow6 As Integer
Dim Data As Range
Dim Cell As Range

Set Data = Range("A1:A1000")
For Each Cell In Range("Data")
If Cell.Interior.ColorIndex = 5 Then
Blue5 = Blue5 + Cell.Value
ElseIf Cell.Interior.ColorIndex = 3 Then
Red3 = Red3 + Cell.Value
ElseIf Cell.Interior.ColorIndex = 6 Then
Yellow6 = Yellow6 + Cell.Value
End If
Next

Range("F10").Value = "Blue = " & Blue5
Range("F11").Value = "Red = " & Red3
Range("F12").Value = "Yellow = " & Yellow6

MsgBox " You have: " & vbCr _
& vbCr & " Blue " & Blue5 _
& vbCr & " Red " & Red3 _
& vbCr & " Yellow " & Yellow6, _
vbOKOnly, "CountColor"

Range("F10:F12").Value = ""

End Sub


HTH
Regards,
Howard

"reza" wrote in message
...
hi all.

i have data with different color.
i one column i have 3 color, blue, yellow and red.
now i want to count & sum based on the colors.
how to create formula for that?

many thanks...

reza



  #5  
Old May 24th, 2010, 10:08 AM posted to microsoft.public.excel.worksheet.functions
reza
external usenet poster
 
Posts: 122
Default sum the color cells

Howard...

i already try, copy your code then paste in module...
but there were a bug in
Run-time error '1004':
method 'Range' of object'_Global' failed
when i click debug, it will go to:
For Each Cell In Range("Data")

thanks



"L. Howard Kittle" wrote:

Give this a try. Copy and right click on the sheet tab and paste in the
sheet module. Provides a summation list in F10, F11 & F12 along with a
Message Bos of the results.

Probably want to try on a test worksheet first.

Sums the values of all Blue, Red and Yellow formatted cells in A1:A1000.

Will not work on conditional formatted cells.

If you are trying to sum the colors of the FONTS of each cell in A:A then
post back. That is quite possible also.

Sub SumColorCount()
Dim Blue5 As Integer, _
Red3 As Integer, _
Yellow6 As Integer
Dim Data As Range
Dim Cell As Range

Set Data = Range("A1:A1000")
For Each Cell In Range("Data")
If Cell.Interior.ColorIndex = 5 Then
Blue5 = Blue5 + Cell.Value
ElseIf Cell.Interior.ColorIndex = 3 Then
Red3 = Red3 + Cell.Value
ElseIf Cell.Interior.ColorIndex = 6 Then
Yellow6 = Yellow6 + Cell.Value
End If
Next

Range("F10").Value = "Blue = " & Blue5
Range("F11").Value = "Red = " & Red3
Range("F12").Value = "Yellow = " & Yellow6

MsgBox " You have: " & vbCr _
& vbCr & " Blue " & Blue5 _
& vbCr & " Red " & Red3 _
& vbCr & " Yellow " & Yellow6, _
vbOKOnly, "CountColor"

Range("F10:F12").Value = ""

End Sub


HTH
Regards,
Howard

"reza" wrote in message
...
hi all.

i have data with different color.
i one column i have 3 color, blue, yellow and red.
now i want to count & sum based on the colors.
how to create formula for that?

many thanks...

reza



.

  #6  
Old May 24th, 2010, 10:18 AM posted to microsoft.public.excel.worksheet.functions
reza
external usenet poster
 
Posts: 122
Default sum the color cells

Howard...

just ignored it my message before... already succeed.
but what i want to achieve is create a real sum in cell, not only message
box and can update every time any changes...
using a real formula...

thanks before


"L. Howard Kittle" wrote:

Give this a try. Copy and right click on the sheet tab and paste in the
sheet module. Provides a summation list in F10, F11 & F12 along with a
Message Bos of the results.

Probably want to try on a test worksheet first.

Sums the values of all Blue, Red and Yellow formatted cells in A1:A1000.

Will not work on conditional formatted cells.

If you are trying to sum the colors of the FONTS of each cell in A:A then
post back. That is quite possible also.

Sub SumColorCount()
Dim Blue5 As Integer, _
Red3 As Integer, _
Yellow6 As Integer
Dim Data As Range
Dim Cell As Range

Set Data = Range("A1:A1000")
For Each Cell In Range("Data")
If Cell.Interior.ColorIndex = 5 Then
Blue5 = Blue5 + Cell.Value
ElseIf Cell.Interior.ColorIndex = 3 Then
Red3 = Red3 + Cell.Value
ElseIf Cell.Interior.ColorIndex = 6 Then
Yellow6 = Yellow6 + Cell.Value
End If
Next

Range("F10").Value = "Blue = " & Blue5
Range("F11").Value = "Red = " & Red3
Range("F12").Value = "Yellow = " & Yellow6

MsgBox " You have: " & vbCr _
& vbCr & " Blue " & Blue5 _
& vbCr & " Red " & Red3 _
& vbCr & " Yellow " & Yellow6, _
vbOKOnly, "CountColor"

Range("F10:F12").Value = ""

End Sub


HTH
Regards,
Howard

"reza" wrote in message
...
hi all.

i have data with different color.
i one column i have 3 color, blue, yellow and red.
now i want to count & sum based on the colors.
how to create formula for that?

many thanks...

reza



.

  #7  
Old May 24th, 2010, 12:23 PM posted to microsoft.public.excel.worksheet.functions
Chip Pearson
external usenet poster
 
Posts: 1,343
Default sum the color cells

Here are two functions that will Sum and Count cells based on color.
In each function, RR is the range to test, ColorIndex is the color
index value to test for, and OfText indicates whether to test the font
color (OfText = True) or the background fill color (OfText = False).

Note that simply changing a cell's formatting or color does not cause
a calculation so you will need to force a calculation to ensure the
values are correct.


Function SumColor(RR As Range, ColorIndex As Long, _
OfText As Boolean) As Double
Dim R As Range
Dim D As Double

For Each R In RR.Cells
If IsNumeric(R.Value) Then
If OfText = True Then
If R.Font.ColorIndex = ColorIndex Then
D = D + R.Value
End If
Else
If R.Interior.ColorIndex = ColorIndex Then
D = D + R.Value
End If
End If
End If
Next R
SumColor = D
End Function

Function CountColor(RR As Range, ColorIndex As Long, _
OfText As Boolean) As Double
Dim R As Range
Dim D As Double

For Each R In RR.Cells
If OfText = True Then
If R.Font.ColorIndex = ColorIndex Then
D = D + 1
End If
Else
If R.Interior.ColorIndex = ColorIndex Then
D = D + 1
End If
End If
Next R
CountColor = D
End Function

You can then call these functions from worksheet cells with formulas
like

=SumColor(A1:A100,3,FALSE)
to sum all entries in A1:A100 that have a background color of red (3).

=CountColor(A1:A100,3,TRUE)
to count all the entries in A1:A100 that have a font color of red (3).

See Help for a list of color index values and their corresponding
color.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com



On Sun, 23 May 2010 23:59:01 -0700, reza
wrote:

hi all.

i have data with different color.
i one column i have 3 color, blue, yellow and red.
now i want to count & sum based on the colors.
how to create formula for that?

many thanks...

reza

  #8  
Old May 24th, 2010, 11:35 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 20,252
Default sum the color cells

You say you have Excel 2007.

You can filter by color and use the SUBTOTAL function.

=SUBTOTAL(9,A1:A100)

Filter for blue and see the sum of blue cells.

=SUBTOTAL(2,A1:A100)

Filter on blue cells and see the count of blue cells.


Gord Dibben MS Excel MVP

On Sun, 23 May 2010 23:59:01 -0700, reza
wrote:

hi all.

i have data with different color.
i one column i have 3 color, blue, yellow and red.
now i want to count & sum based on the colors.
how to create formula for that?

many thanks...

reza


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:55 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.