Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional formatting and then counting specifics within it.
I have a work book that can have up to 100 sheets within it.
An example of a sheet is:- Col B Col F Col N Col AM Col AN Col AO Col AP Test 1 12/03/03 1 01/01/03 31/03/03 01/01/02 31/03/02 Test 2 01/01/03 5 01/04/03 31/08/03 01/04/02 31/08/02 Test 3 01/09/03 2 01/09/03 31/12/03 01/09/02 31/12/02 Test 4 12/12/03 2 Test 5 14/04/03 3 Test 6 12/03/03 1 Column B hold a name. Column F holds a date of birth. Column N hold a number in the range 1 to 5. Columns AM to AP are start and end dates for current year and previous year for specific periods in the format dd/mm/yy. I have used 3 Conditional formatting statements in column F to basically colour code it by using dates in specified in columns Am to AP. What I need is to count the items reported by Conditional Format 1, and then count the total number of 1 to 5 that are present in that range. I then need the same reported by Conditional Format 2 and 3. Therefore for the above data I would expect conditional format 1 to highlight 3 records (Test 1, Test 2 and Test 6) with the following counts Range 1;2 Range 2;0 Range 3;0 Range 4;0 Range 5;1. Conditional format 2 would highlight 1 records (Test 5) with the following counts Range 1;0 Range 2;0 Range 3;1 Range 4;0 Range 5;0. Conditional format 3 would highlight 2 records (Test 3, Test 4) with the following counts Range 1;0 Range 2;2 Range 3;0 Range 4;0 Range 5;0. What ever is required to achieve the above will be inserted in a macro that will be run against all sheets in the workbook. Any assistance offered would be appreciated. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional formatting and then counting specifics within it.
Any formula you use in Conditional Formatting can be as a "condition" in
SUMIF, SUMPRODUCT, etc. Suppose Format 1 is AND(AN1date(2003,1,1),AP1<date(2003,10,1) The to count the number of cells that satisfy these conditions =SUMPRODUCT(--(AN1:AN100date(2003,1,1),--(AP1:AP100<date(2003,10,1)) see http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://mcgimpsey.com/excel/formulae/doubleneg.html If you tell us more about the CF formulas we can be more specific best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Pank" wrote in message ... I have a work book that can have up to 100 sheets within it. An example of a sheet is:- Col B Col F Col N Col AM Col AN Col AO Col AP Test 1 12/03/03 1 01/01/03 31/03/03 01/01/02 31/03/02 Test 2 01/01/03 5 01/04/03 31/08/03 01/04/02 31/08/02 Test 3 01/09/03 2 01/09/03 31/12/03 01/09/02 31/12/02 Test 4 12/12/03 2 Test 5 14/04/03 3 Test 6 12/03/03 1 Column B hold a name. Column F holds a date of birth. Column N hold a number in the range 1 to 5. Columns AM to AP are start and end dates for current year and previous year for specific periods in the format dd/mm/yy. I have used 3 Conditional formatting statements in column F to basically colour code it by using dates in specified in columns Am to AP. What I need is to count the items reported by Conditional Format 1, and then count the total number of 1 to 5 that are present in that range. I then need the same reported by Conditional Format 2 and 3. Therefore for the above data I would expect conditional format 1 to highlight 3 records (Test 1, Test 2 and Test 6) with the following counts Range 1;2 Range 2;0 Range 3;0 Range 4;0 Range 5;1. Conditional format 2 would highlight 1 records (Test 5) with the following counts Range 1;0 Range 2;0 Range 3;1 Range 4;0 Range 5;0. Conditional format 3 would highlight 2 records (Test 3, Test 4) with the following counts Range 1;0 Range 2;2 Range 3;0 Range 4;0 Range 5;0. What ever is required to achieve the above will be inserted in a macro that will be run against all sheets in the workbook. Any assistance offered would be appreciated. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional formatting and then counting specifics within it.
Bernard,
Thank you for your assistance. The conditional statements I have that runs as part of a macro is :- Columns("F:F").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _ Formula1:="=$AM$2", Formula2:="=$AN$2" Selection.FormatConditions(1).Interior.ColorIndex = 38 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _ Formula1:="=$AM$3", Formula2:="=$AN$3" Selection.FormatConditions(2).Interior.ColorIndex = 40 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _ Formula1:="=$AM$4", Formula2:="=$AN$4" Selection.FormatConditions(3).Interior.ColorIndex = 36 Any further assistance you can offer would be appreciated. "Bernard Liengme" wrote: Any formula you use in Conditional Formatting can be as a "condition" in SUMIF, SUMPRODUCT, etc. Suppose Format 1 is AND(AN1date(2003,1,1),AP1<date(2003,10,1) The to count the number of cells that satisfy these conditions =SUMPRODUCT(--(AN1:AN100date(2003,1,1),--(AP1:AP100<date(2003,10,1)) see http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://mcgimpsey.com/excel/formulae/doubleneg.html If you tell us more about the CF formulas we can be more specific best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Pank" wrote in message ... I have a work book that can have up to 100 sheets within it. An example of a sheet is:- Col B Col F Col N Col AM Col AN Col AO Col AP Test 1 12/03/03 1 01/01/03 31/03/03 01/01/02 31/03/02 Test 2 01/01/03 5 01/04/03 31/08/03 01/04/02 31/08/02 Test 3 01/09/03 2 01/09/03 31/12/03 01/09/02 31/12/02 Test 4 12/12/03 2 Test 5 14/04/03 3 Test 6 12/03/03 1 Column B hold a name. Column F holds a date of birth. Column N hold a number in the range 1 to 5. Columns AM to AP are start and end dates for current year and previous year for specific periods in the format dd/mm/yy. I have used 3 Conditional formatting statements in column F to basically colour code it by using dates in specified in columns Am to AP. What I need is to count the items reported by Conditional Format 1, and then count the total number of 1 to 5 that are present in that range. I then need the same reported by Conditional Format 2 and 3. Therefore for the above data I would expect conditional format 1 to highlight 3 records (Test 1, Test 2 and Test 6) with the following counts Range 1;2 Range 2;0 Range 3;0 Range 4;0 Range 5;1. Conditional format 2 would highlight 1 records (Test 5) with the following counts Range 1;0 Range 2;0 Range 3;1 Range 4;0 Range 5;0. Conditional format 3 would highlight 2 records (Test 3, Test 4) with the following counts Range 1;0 Range 2;2 Range 3;0 Range 4;0 Range 5;0. What ever is required to achieve the above will be inserted in a macro that will be run against all sheets in the workbook. Any assistance offered would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting cells based on conditional formatting | Excel Discussion (Misc queries) | |||
How do I lookup data and result specifics? | Excel Discussion (Misc queries) | |||
Counting Cells with Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional counting | Excel Discussion (Misc queries) | |||
counting cells with conditional formatting applied | Excel Discussion (Misc queries) |