Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Cells by Format
I have some large spreadsheets with rows of expected results for a specific
data element under various conditions. People entered whether or not the expected results were received by changing the fill on the cell to green, yellow or red. I need to count the different results into Pass, Doc, and Fail. Help!! Here is the situation: X-Axis = Element Name Y-Axis = Condition. Cells contain either Y; N; or n/a Row 1 has 5 cells = Red; 150 cells = Green; 30 cells = Yellow. Row 2 has 15 cells = Red; 120 cells = Green; 50 cells = Yellow. How don't care about the Cell values, but do care about the results (color). |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Cells by Format
hi
you need a udf for that. see this site. look for countbycolor http://cpearson.com/excel/colors.aspx there is a sum by color too. Regards FSt1 "jansroots" wrote: I have some large spreadsheets with rows of expected results for a specific data element under various conditions. People entered whether or not the expected results were received by changing the fill on the cell to green, yellow or red. I need to count the different results into Pass, Doc, and Fail. Help!! Here is the situation: X-Axis = Element Name Y-Axis = Condition. Cells contain either Y; N; or n/a Row 1 has 5 cells = Red; 150 cells = Green; 30 cells = Yellow. Row 2 has 15 cells = Red; 120 cells = Green; 50 cells = Yellow. How don't care about the Cell values, but do care about the results (color). |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Cells by Format
Here is a version to that, will need some tweaking adapt to the range of
cells your are coloring. Looks like you can eliminate Blue5 also. Does not work with conditional formatting colors. Sub ColorCount() 'Counts the number of colored 'cells in a range named Data. Dim Blue5 As Integer Dim Red3 As Integer Dim Green4 As Integer Dim Yellow6 As Integer Dim Cell As Range For Each Cell In Range("Data") '("B1:F11") If Cell.Interior.ColorIndex = 5 Then Blue5 = Blue5 + 1 ElseIf Cell.Interior.ColorIndex = 3 Then Red3 = Red3 + 1 ElseIf Cell.Interior.ColorIndex = 4 Then Green4 = Green4 + 1 ElseIf Cell.Interior.ColorIndex = 6 Then Yellow6 = Yellow6 + 1 End If Next Range("A1").Value = Blue5 & " Blue" Range("A2").Value = Red3 & " Red" Range("A3").Value = Green4 & " Green" Range("A4").Value = Yellow6 & " Yellow" MsgBox " You have: " & vbCr _ & vbCr & " Blue " & Blue5 _ & vbCr & " Red " & Red3 _ & vbCr & " Green " & Green4 _ & vbCr & " Yellow " & Yellow6, _ vbOKOnly, "CountColor" End Sub HTH Regards, Howard "jansroots" wrote in message ... I have some large spreadsheets with rows of expected results for a specific data element under various conditions. People entered whether or not the expected results were received by changing the fill on the cell to green, yellow or red. I need to count the different results into Pass, Doc, and Fail. Help!! Here is the situation: X-Axis = Element Name Y-Axis = Condition. Cells contain either Y; N; or n/a Row 1 has 5 cells = Red; 150 cells = Green; 30 cells = Yellow. Row 2 has 15 cells = Red; 120 cells = Green; 50 cells = Yellow. How don't care about the Cell values, but do care about the results (color). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting the number of cells between 2 seperate cells | Excel Discussion (Misc queries) | |||
COUNTING BASED ON CONDITIONAL FORMAT? | Excel Worksheet Functions | |||
Counting based on format | Excel Worksheet Functions | |||
want format cells alignment not format cells font style | Excel Discussion (Misc queries) | |||
Complicated counting of cells (based on other cells contents) | Excel Worksheet Functions |