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.





0
Pank (119)
3/8/2007 2:32:18 PM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
784 Views

Similar Articles

[PageSpeed] 41

Any formula you use in Conditional Formatting can be as a "condition" in 
SUMIF, SUMPRODUCT, etc.

Suppose Format 1 is AND(AN1>date(2003,1,1),AP1<date(2003,10,1)
The to count the number of cells that satisfy these conditions
=SUMPRODUCT(--(AN1:AN100>date(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" <Pank@discussions.microsoft.com> wrote in message 
news:6299C515-497E-488D-9B65-916C6B093581@microsoft.com...
>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.
>
>
>
>
> 


0
bliengme5824 (3040)
3/8/2007 3:33:22 PM
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(AN1>date(2003,1,1),AP1<date(2003,10,1)
> The to count the number of cells that satisfy these conditions
> =SUMPRODUCT(--(AN1:AN100>date(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" <Pank@discussions.microsoft.com> wrote in message 
> news:6299C515-497E-488D-9B65-916C6B093581@microsoft.com...
> >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.
> >
> >
> >
> >
> > 
> 
> 
> 
0
Pank (119)
3/13/2007 11:32:00 AM
Reply:

Similar Artilces:

Help with conditional formatting with 2000
Any help would be greatly appreciated. I am trying to group data together into increments of 10% of th numbers and then chart them based on these groups. For example, I hav 300 data points that vary from 20 to 500 in value. I want them t appear in a chart based on the number of values that fall in the lowes 10% of numbers (ie. 20-40) then the next 10% (ie. 40-60) etc. up to th top 10% of numbers, but I do not want to manually determine what thes ranges are. I want to see a distribution of how many numbers fal within each 10% of values. I am not sure if this makes sense, please let me know...

Date Formatting when Concantenating
I have a simple question. I have a cell that has date that looks lik this: 10/15/1999 14:34 When I use the concantenate feature my date looks like this: 36448.6073611111 I tried to format the call every which way - but I cannot get it t look the original. Feeling really silly for even asking.. thanks all for your help -- Message posted from http://www.ExcelForum.com Hi bleu808! Use: ="Today is "&TEXT(TODAY(),"mm/dd/yyyy hh:mm") -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au "bleu808 >" <<bleu808.189yij...

Forwarding a specific e-mail to an external e-mail!
The owner of the company would like to forward an e-mail received from a specific customer's e-mail address to his external e-mail - This is the process - Our customer (cust@123.com) sends and e-mail to the owner of the company (owner@company.com). Whenever the owner of the company receives e-mail from our customer cust@123.com, the e-mail should automatically be forward to the owner's external e-mail (owner@yahoo.com) How do I do this? I tried a forwarding rule and it didn't work. Do you have any other suggestions on how to accomplish this? Thank you in advance for you...

1 Chart
I presently have an XY line chart showing asset price over time. Pretty simple. X Axis - Time Scale Y Axis - Asset Price I would now like to add an additoinal series showing the volume of assets traded, ideally this would be as a bar chart sitting "underneath" the asset price on the chart. They would share the same X Axis. I have added another series, but this simply displays the volume traded as another line, and even when this is set to a secondary axis the scaling makes this unworkable. i have adjusted the scales of both, still this does not make it workable, i want the series...

Word 2007: word count wrong?
Hello, I have an issue with some .doc files when opening in Word 2007. In some cases the word count in the status bar is different of the word count of the "Word Count"- window (CTRL+SHIFT+G) or the word count in Word 2003 Example file: http://go.microsoft.com/fwlink/?LinkId=79595 Word 2007 (status bar) show 61019 words Word 2007 (Word count window) show 61010 words Word 2003 also show 61010 words This issue I have not with all documents, but only with some files and it seems that I have this problem only with .doc files but not with .docx files. (installed ve...

Report Format
Env: CRM 3, VS 2003, remote connect to CRM server. I've created a QUOTE in VS2003 and when viewed using PREVIEW from within the VS environment it produces a beautiful QUOTE (at least in my opinion). When printed, again from the VS environment, it is perfect. When I upload the RDL file to the CRM server, create an new REPORT using this RDL then produce a REPORT, the formatting is all over the place. When I inspect the source (in VS) there are no fields that extend past the defined size. It appers that the CRM report engine randomly adds CRLFs all thru the report ... ??? ...

How can I clear the last Data->Text to columns to formatting
I've noticed in Excel 2000 that if I paste text into various worksheets within a workbook each paste will assume the Text->Column formatting that I applied in the previous. How can I prevent it from happening ? Thanks Steve Just run another data|Text to columns against a dummy cell. Specify delimited, but remove all the check marks from all the possible delimiters. (alternatively, you can close excel and reopen it.) svaardt wrote: > > I've noticed in Excel 2000 that if I paste text into various worksheets > within a workbook each paste will assume the Text->Col...

Finding characters within a text
Hi How to check, using single formula, that a text within a certain cell contains one of certain characters? For instance, how to check if there is a 'R', 'L' or 'Ps' character within cell A1 that reads 'W-RII'. Thanks in advance Hmm..not very elegant, but maybe =NOT(AND(ISERROR(FIND("R",A1)),ISERROR(FIND("L",A1)),ISERROR(FIND("P",A1)))) Returns TRUE if the text in A1 contains 'R', 'L' or 'Ps' . FIND is case-sensitive, use SEARCH if you also want to find lower-case characters. Cheers, Joerg Mochiku...

Simple Access counting queries
Hi, hoping someone can help a relative newbie with a pretty simple query. My database (Access 2007) has three tables: Customers Products Purchases (many-to-one links to both of the other tables, this is basically a linking table) I have two simple queries I'd like to get out of this database, but I'm a bit stuck on how to construct the SQL. Any direction you can give me would be helpful. 1. List of all customers who have purchased 2 or more products (or 3 or more products, or 4+, etc.) 2. List of all customers who have purchased both Product A and Product B (or A, B, and C, or B an...

Count # of cells b/w cells ...
Hello, I have the following data in a column: 7 0 0 0 7 0 0 0 0 0 7 0 0 7 0 0 0 0 0 0 7 etc. The number of zero's between the 7's is random. I want a formula tha would count the number of zeros between the 7's. Thanks, Ari Bar -- AriBar ----------------------------------------------------------------------- AriBari's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2504 View this thread: http://www.excelforum.com/showthread.php?threadid=38806 Assume A5:A20 is the data, try this: B5 = A5+B4 (copy formula down) Now make a table with 2 column...

Too many different cell formats #6
I am running into the error message: Too many different cell formats Is there a solution to lowering the number of formats I am using? Just trying to change them to make some consistent gives me the same error message. I tried running the search on the forums on my topic but they have been disabled for a Microsoft upgrade. Thanks! One idea - Rob Bovey's excellent Utilities add-in will list all the formats in use in your workbook, allowing you to manually delete what isn't being used. http://www.appspro.com/Utilities/ExcelUtilities.htm You can also see the source code for ...

date/time formatting
I download a csv report from a web based program. Everything works great but the date comes through as "Jul 21 2009 1:51pm". I do not need the time in my report. I have tried reformatting the cells, tried to copy to a new file with the cells already formatted to "7/21/09", opening the cell format using the F2 key and deleting the time (time will still not be formatted correctly). The only way I get get just the time is to retype every cell. What am I doing wrong? I am using Excel 2002 SP3 TIA, Cindy When data is downloaded from the web, a lot of "other&quo...

Conferting File format
One of my users stored 250 photos on CD in a pdf format. The person that will work with them wants them in a raw format. Is there any way to convert the entire disk at one time to a raw format, rather than opening each file and saving in the new format? Thank you. vsp deborah <vspdeborah@discussions.microsoft.com> was very recently heard to utter: > One of my users stored 250 photos on CD in a pdf format. The person > that will work with them wants them in a raw format. Is there any way > to convert the entire disk at one time to a raw format, rather than > opening each f...

format cell #4
In Access, I can set up a field that "forces" the user to enter info - a date, for example - in a certain way, such as 25 Jan 05 or enter time as 12:15 AM. Is there a way that I can "force" this in excel? Thank you. Hello- Without invoking something more technical, you can select the cell(s) and go to Data>Validation and choose what type of entry be allowed in the field. Format the cell in the manner you wish to have the date or time expressed. HTH |:>) "HJC" wrote: > In Access, I can set up a field that "forces" the user to enter in...

count if a match occurs
Hi! I have two rows - say Row 1 and Row 2. In the first row I have a answer key. In the second row I have answers from a student. I would like to write a formula where it counts how many answers student got write. Here is a example: Row 0: Q1 Q2 Q3 Q4 Q5 Q6 Row 1: 1 4 2 3 4 1 Row 2: 3 4 1 3 4 1 So student marked 4 questions correctly: Q2, Q4, Q5 and Q6. Hence, the formula should return 4. I know one way to do it but I am sure there is more efficient way to do it. The way I know: Use if statement to compare two corresponding entries and output 1 if...

How to get only the year in the date format in Access
How to get only the year in the date format I.e in the table in need to display only year E.g 2005 - should be display " 05" automatically Custom format the cell as: yy -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "yanu" <yanu@discussions.microsoft.com> wrote in message news:14CE9F60-F7B9-467A-8C16-71088C31BEBA@microsoft.com... > How to get only the year in the date form...

unicode format files for Outlook 2002
I use Outlook 2002. I would like to use Unicode files (for larger size). I see references to using it with Outlook 2003, but none for 2002. can unicode files be used with Outlook 2002? If so, how? thanks, Huck No OL 2003 & 2007 only "Huck Rorick" <huckrorick@groundwork.org> wrote in message news:epYUYAl4IHA.2348@TK2MSFTNGP06.phx.gbl... >I use Outlook 2002. I would like to use Unicode files (for larger size). >I see references to using it with Outlook 2003, but none for 2002. can >unicode files be used with Outlook 2002? If so, how? > > than...

Formatting cells and getting pound signs
I am using Excel 2003 with all updates as of 4/28/04 and trying to format a cell using the custom category and choosing the #,##0.00 type. I am trying to add the $ symbol at the beginning of the type and add text at the end of the type to look like this $#,##0.00 "text". When I do this however it shows up in my cell on my worksheet as ##########. It does know what the value is and shows as I would expect it to when I place mouse over cell in a balloon If I use only the $ symbol befor the type it shows fine. If I use only the "text" after the type is shows fine. Using the...

Count Age Grouping
I have an access 2k database in which I need to count groups of records of individuals by that age groups such as 14- 20 no of individuals 21-30 no of individuals 31-40 no of individuals 41-50 no of individuals 51-60 no of individuals 61-70 no of individuals 71-80 no of individuals 80+ no of individuals I have both DOB and Age fields in the table I have tried several queries but with no luck and ideas On 19 Mar 2007 16:51:49 -0700, "Nemesis_uk" <nemesis_uk@ntlworld.com> wrote: >I have an access 2k ...

Count the text in a column
I would like to count the text in a column then for it to add a figure in another cell if it meets the text criteria Thanks! Do you mean count the characters? If so =SUM(NOT(ISNUMBER(A1:A20))*LEN(A1:A20)) as an array formula (committed with Ctrl-Shift-Enter) -- HTH RP (remove nothere from the email address if mailing direct) "Peter Curtis" <PeterCurtis@discussions.microsoft.com> wrote in message news:94C093C4-38DC-4989-846A-9352F3298B7C@microsoft.com... > I would like to count the text in a column then for it to add a figure in > another cell if it meets the t...

Conditional Formatting on cells beginning with a hyphen
Is it possible to do conditional formatting on cells beginning with a hyphen? Thanks, Greg 1. Place the cursor in A1 cell and select the Range 2. From menu Format>Conditional Formatting> 3. For Condition1>Select 'Formula Is' and paste the below formula =LEFT(A1,1)="-" 4. Click Format Button>Font>Color select your desired font & Background Color pattern and then give ok Change the cell reference of A1 to your desired cell, if required. But keep in mind that when applying the conditional formatting the Active cell should be in the ce...

Selecting cell value for a sum, based on a condition
Trying to come up with a formula or method that will enable me to sum values based on a condition. For example, I have three columns which contain a condition and two amounts. If the condition is of the 'each' variety, one value will be used in the sum. If the condition is of the "square foot" variety, another value will be used. Here is a small diagram that may help visualize this: A B C D 1 Measure Unit Cost S.F. Cost Summed Total 2 Each 3.00 .30 3 S.F....

time formats #3
I have an Excel sheet with a long list of times spent on various projects. The times should all be in minutes and seconds. The first time reads 2:29 and I know that is accurate, for 2 min, 29 seconds. In the formula bar, it reads, 2:29:00 AM. Another...the cell displays 0:40 and I know that is right, for 40 seconds. But in the formula bar, I see 12:40:00 AM I want numbers, but apparently I am looking at times. I tried changing the format. When I check the format for 2:29, it comes up Custom and says it is hh:mm, not mm:ss. I tried changing it to mm:ss, but that changes the displa...

Does anyone have a format or template for a Education Cover Letter
Does anyone have a format or template for a Education Cover Letter? To Whom It May Concern: Enclosed please find an education. Sincerely, eb -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "eb" <eb@discussions.microsoft.com> wrote in message news:4CEF7B84-6F7A-4D07-97B3-F349F3B11E54@microsoft.com... > Does anyone have a format or template for a Education Cover Letter? > "Suzanne S. Barnhill" <sbarnhill@mvps.org> wrote in message news:O3aNFsq2KHA.5212@TK2MSFTNGP04.phx.g...

Excel Number Format Codes
I can't figure out how to format numbers so that when you type 3220 it will look like 32.20 kinda like how you can enter numbers on an adding machine. I would greatly appreciate this number format code. Thank you. -- 1:~ Hi, I think you want to go Tools | Options | Edit | Fixed Decimals - 2. That will divide all the numbers you enter by 100. You will also have to format the row or column to display 2 decimals. Select the cells or range, right mouse click, format cells, numbers, 2 decimals. Hope that helps. Best regards, Kevin "MBB" <MBB@discussions.microsoft.com> w...