Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countifs for color format
I have columns AA through AK that are conditionally formatted to either a red
fill or a green fill. How can I enter countifs in a column to tell me how many Green fills there are in a given row. So if for a particular row only column AA has a green and AD has a green, it will tell me "2" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countifs for color format
Your answer is he
http://www.cpearson.com/excel/CFColors.htm This is good too: http://www.cpearson.com/excel/colors.aspx HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Doug" wrote: I have columns AA through AK that are conditionally formatted to either a red fill or a green fill. How can I enter countifs in a column to tell me how many Green fills there are in a given row. So if for a particular row only column AA has a green and AD has a green, it will tell me "2" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countifs for color format
Do I place the Function GetStrippedValue in the sheet view code? I have
placed it in the view code and entered =CountOfCF($AA3:$AK3,2) to show number of green filled and it shows #name. I noticed that Pearson says: NOTE: ActiveCondition may result in an inaccurate result if the following are true: * You are calling ActiveCondtion from a worksheet cell, AND * The cell passed to ActiveCondtion uses a "Formula Is" rather than "Cell Value Is" condition, AND * The formula used in the condition formula contains relative addresses The formula I am using is "above average" for columns AA Through AK. Is this why it is not working? -- "ryguy7272" wrote: Your answer is he http://www.cpearson.com/excel/CFColors.htm This is good too: http://www.cpearson.com/excel/colors.aspx HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Doug" wrote: I have columns AA through AK that are conditionally formatted to either a red fill or a green fill. How can I enter countifs in a column to tell me how many Green fills there are in a given row. So if for a particular row only column AA has a green and AD has a green, it will tell me "2" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countifs for color format
You need to copy more than one function from Chip's site.
Getstrippedvalue Activecondition Countofcf Place these in a general module in your workbook. All of this may be unnecessary. Can't you just count by condition rather than color? In Z1 enter =AVERAGE(AA3:AK3) Then in another =COUNTIF($AA3:$AK3"Z1") Gord Dibben MS Excel MVP On Fri, 11 Dec 2009 10:15:01 -0800, Doug wrote: Do I place the Function GetStrippedValue in the sheet view code? I have placed it in the view code and entered =CountOfCF($AA3:$AK3,2) to show number of green filled and it shows #name. I noticed that Pearson says: NOTE: ActiveCondition may result in an inaccurate result if the following are true: * You are calling ActiveCondtion from a worksheet cell, AND * The cell passed to ActiveCondtion uses a "Formula Is" rather than "Cell Value Is" condition, AND * The formula used in the condition formula contains relative addresses The formula I am using is "above average" for columns AA Through AK. Is this why it is not working? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countifs for color format
After putting the three functions into a module, when I reset the value in
the column it says "Ambiguous name detected: CountOfCF" -- Thank you! "Gord Dibben" wrote: You need to copy more than one function from Chip's site. Getstrippedvalue Activecondition Countofcf Place these in a general module in your workbook. All of this may be unnecessary. Can't you just count by condition rather than color? In Z1 enter =AVERAGE(AA3:AK3) Then in another =COUNTIF($AA3:$AK3"Z1") Gord Dibben MS Excel MVP On Fri, 11 Dec 2009 10:15:01 -0800, Doug wrote: Do I place the Function GetStrippedValue in the sheet view code? I have placed it in the view code and entered =CountOfCF($AA3:$AK3,2) to show number of green filled and it shows #name. I noticed that Pearson says: NOTE: ActiveCondition may result in an inaccurate result if the following are true: * You are calling ActiveCondtion from a worksheet cell, AND * The cell passed to ActiveCondtion uses a "Formula Is" rather than "Cell Value Is" condition, AND * The formula used in the condition formula contains relative addresses The formula I am using is "above average" for columns AA Through AK. Is this why it is not working? . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countifs for color format
So you have decided to go with the color functions rather than the Z1
=AVERAGE(range_to_average)? The error message "ambiguous name" would indicate you have duplicate CountOfCF functions or you have named a module CountOfCF. Do you now have the three functions in a general module? Did you delete the functions you had placed in in the sheet module? Gord On Fri, 11 Dec 2009 19:23:01 -0800, Doug wrote: After putting the three functions into a module, when I reset the value in the column it says "Ambiguous name detected: CountOfCF" |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countifs for color format
Thank you for your reply...
I decided that the use of the CountOfCF would serve me better. =CountOfCF($AA3:$AK3,2) I have these all the way down the column 3 to 1000. I tried changing the fill color of RGB to 0,255,0 just to make sure that wasn't a problem. I checked the modules to make sure there is not one with the name countofcf and there was not. I tried the three functions in a general module and when it didn't work I just placed them all in there. In order, I have: Function ActiveCondition, Function ColorIndexOfCF, Function ColorOfCF, Function GetStrippedValue, Function CountOfCF, Function SumByCFColorIndex -- Thank you! "Gord Dibben" wrote: So you have decided to go with the color functions rather than the Z1 =AVERAGE(range_to_average)? The error message "ambiguous name" would indicate you have duplicate CountOfCF functions or you have named a module CountOfCF. Do you now have the three functions in a general module? Did you delete the functions you had placed in in the sheet module? Gord On Fri, 11 Dec 2009 19:23:01 -0800, Doug wrote: After putting the three functions into a module, when I reset the value in the column it says "Ambiguous name detected: CountOfCF" . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countifs for color format
Does this mean you are all sorted out or just adding more info to help in
trouble-shooting? If you are still getting the "ambiguous name" then you haven't looked hard enough or in the right place(s). If you desire, please email me the workbook to gorddibbATshawDOTca change the obvious. Gord On Wed, 16 Dec 2009 07:38:01 -0800, Doug wrote: Thank you for your reply... I decided that the use of the CountOfCF would serve me better. =CountOfCF($AA3:$AK3,2) I have these all the way down the column 3 to 1000. I tried changing the fill color of RGB to 0,255,0 just to make sure that wasn't a problem. I checked the modules to make sure there is not one with the name countofcf and there was not. I tried the three functions in a general module and when it didn't work I just placed them all in there. In order, I have: Function ActiveCondition, Function ColorIndexOfCF, Function ColorOfCF, Function GetStrippedValue, Function CountOfCF, Function SumByCFColorIndex |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional format color is set correct, yet color will not change | Excel Worksheet Functions | |||
Countifs to Sumproduct for 2003 - was Simple Countifs.. from Fr | Excel Worksheet Functions | |||
Converting COUNTIFS to 2003 format | Excel Worksheet Functions | |||
Can't format cell color/text color in Office Excel 2003 in fil | Excel Discussion (Misc queries) | |||
Can't format cell color/text color in Office Excel 2003 in files . | Excel Discussion (Misc queries) |