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.