Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Recognize Cell Color and Sum Data accordingly
I've tried SUMIFS to recognize cell colors and add the numbers (which are
hours worked in the day) in the corresponding row. The kicker is the yellow cell doesn't necessarily have the same hours and I can't get Excel to recognize colors only for one of my criteria. It could range from 2 to 12 hours for any given day. The row may contain data with up to five different colors associated depending on the kind of work performed that day. So let's say I want to pull all yellow cells for a given time period and it looks something like the following. A B C D E F Yellow Cell Blue Cell Yellow Cell Red Cell Black Cell 1 1/1 1/2 1/3 1/4 1/5 2 4-Man Crew 10 10 8 8 6 Indications below column headers in example above are not in spreadsheet. Could not color B2 Yellow, C2 Blue, D2 Yellow for these purposes. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Recognize Cell Color and Sum Data accordingly
Take a look at this link...
http://www.cpearson.com/excel/colors.aspx -- HTH... Jim Thomlinson "Universal Pegasus - Chad" wrote: I've tried SUMIFS to recognize cell colors and add the numbers (which are hours worked in the day) in the corresponding row. The kicker is the yellow cell doesn't necessarily have the same hours and I can't get Excel to recognize colors only for one of my criteria. It could range from 2 to 12 hours for any given day. The row may contain data with up to five different colors associated depending on the kind of work performed that day. So let's say I want to pull all yellow cells for a given time period and it looks something like the following. A B C D E F Yellow Cell Blue Cell Yellow Cell Red Cell Black Cell 1 1/1 1/2 1/3 1/4 1/5 2 4-Man Crew 10 10 8 8 6 Indications below column headers in example above are not in spreadsheet. Could not color B2 Yellow, C2 Blue, D2 Yellow for these purposes. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Recognize Cell Color and Sum Data accordingly
Try this for the yellow cells which may get you started. You will need to
name the range from which you want to add the yellow cells values to DataY. Select the cells on the sheet and in the name box enter DataY then Enter. Copy this code and paste in the VB editor and give it a go. Sums the value in each yellow cell in DataY and displays in a message box and on the worksheet. Sub SumColorCountYellow() Dim Yellow6 As Integer Dim Cell As Range For Each Cell In Range("DataY") If Cell.Interior.ColorIndex = 6 Then Yellow6 = Yellow6 + Cell.Value End If Next Range("F1").Value = "Yellow = " & Yellow6 MsgBox " Yellow adds to " & Yellow6, _ vbOKOnly, "CountColor" Range("F1").Value = "" End Sub You might want to check this out if you, say have Orange, Red and Green, as an example. Name the range to test this Data. Sums the value in each color in Data and displays in a message box and on the worksheet. Both will need to tweeked to suit your final worksheet and you should test these on a test worksheet and neither will work if the cells are colored by conditional formatting. Sub SumColorCount() Dim Orange46 As Integer, _ Red3 As Integer, _ Green4 As Integer Dim Cell As Range For Each Cell In Range("Data") If Cell.Interior.ColorIndex = 46 Then Orange46 = Orange46 + Cell.Value ElseIf Cell.Interior.ColorIndex = 3 Then Red3 = Red3 + Cell.Value ElseIf Cell.Interior.ColorIndex = 4 Then Green4 = Green4 + Cell.Value End If Next Range("F10").Value = "Orange = " & Orange46 Range("F11").Value = "Red = " & Red3 Range("F12").Value = "Green = " & Green4 MsgBox " You have: " & vbCr _ & vbCr & " Orange " & Orange46 _ & vbCr & " Red " & Red3 _ & vbCr & " Green " & Green4, _ vbOKOnly, "CountColor" Range("F10").Value = "" Range("F11").Value = "" Range("F12").Value = "" End Sub HTH Regards, Howard "Universal Pegasus - Chad" <Universal Pegasus - wrote in message ... I've tried SUMIFS to recognize cell colors and add the numbers (which are hours worked in the day) in the corresponding row. The kicker is the yellow cell doesn't necessarily have the same hours and I can't get Excel to recognize colors only for one of my criteria. It could range from 2 to 12 hours for any given day. The row may contain data with up to five different colors associated depending on the kind of work performed that day. So let's say I want to pull all yellow cells for a given time period and it looks something like the following. A B C D E F Yellow Cell Blue Cell Yellow Cell Red Cell Black Cell 1 1/1 1/2 1/3 1/4 1/5 2 4-Man Crew 10 10 8 8 6 Indications below column headers in example above are not in spreadsheet. Could not color B2 Yellow, C2 Blue, D2 Yellow for these purposes. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula won't recognize data | Excel Worksheet Functions | |||
Recognize Color and Insert Symbol | Excel Worksheet Functions | |||
How to recognize changes in slope of data? | Excel Discussion (Misc queries) | |||
Format a cell to recognize data and change font colors accordingly | Excel Worksheet Functions | |||
recognize the cell | Excel Discussion (Misc queries) |