Count the cels with the same collor made with conditional formatting!

Hi all,

Who nows how i can count the collor cels (red-green-yellow) in my worksheet!
I'm using conditional formating to collor them.
I need the sum of all cels with the same color to put them in a chart.

Thanks!

Greetz,

Jan ;-))


0
Jan.L (1)
3/7/2004 9:35:38 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
966 Views

Similar Articles

[PageSpeed] 29

Hi Jan
have a look at this previous discussion
http://tinyurl.com/3xr9b

--
Regards
Frank Kabel
Frankfurt, Germany

Freesurfer wrote:
> Hi all,
>
> Who nows how i can count the collor cels (red-green-yellow) in my
> worksheet! I'm using conditional formating to collor them.
> I need the sum of all cels with the same color to put them in a
chart.
>
> Thanks!
>
> Greetz,
>
> Jan ;-))

0
frank.kabel (11126)
3/7/2004 10:04:33 PM
On Sun, 07 Mar 2004 21:35:38 GMT, "Freesurfer" <Jan.L@advalvas.be> wrote:

>Hi all,
>
>Who nows how i can count the collor cels (red-green-yellow) in my worksheet!
>I'm using conditional formating to collor them.
>I need the sum of all cels with the same color to put them in a chart.
>
>Thanks!
>
>Greetz,
>
>Jan ;-))
>

You need to count them using the same formula that you used for conditional
formatting.

If that formula is simple, COUNTIF could be used.

If that formula is complex, then an array formula or SUMPRODUCT may be
required.

If you post the conditional formatting formula, we can probably help.


--ron
0
ronrosenfeld (3122)
3/7/2004 10:11:25 PM
Reply:

Similar Artilces:

how to format :send picture with message: as a default
each time i sent a graphic i must go to the format and click "send picture with message." how do I make this a default so it is there permanently.I am running on a Windows XP and am using Outlook Express for my mail. LYNNY <LYNNY@discussions.microsoft.com> wrote: > each time i sent a graphic i must go to the format and click "send > picture with message." how do I make this a default so it is there > permanently.I am running on a Windows XP and am using Outlook Express > for my mail. Ask in an Outlook Express newsgroup. -- Brian Tillman ...

count between a date range
Hi, I want to be able to count between a date range and return the value yes. I will show you the formula I currently have: =IF(M2>=DateCell-365,"Yes","No") This gives a yes if the date is greater than the date in M2 minus 365. This however cincludes all the dates beyond M2 which isnt what I want. I want the dates upto M2. If that makes sense? Any help would be greatly appreciated Thanks Try.. =IF(M2>=datecell-DATE(1,0,0),"Yes","No") -- Jacob (MVP - Excel) "Dave" wrote: > Hi, I want to be able to count be...

FORMATING COLUMNS..... HELP
I need to format columns to allow only 7 characters and the rest of the data to go into column B -- JTEFUN "JTEFUN" <JTEFUN@discussions.microsoft.com> wrote in message news:6C5C7A99-E83F-430E-9576-6D1DB57B9311@microsoft.com... >I need to format columns to allow only 7 characters and the rest of the >data > to go into column B > -- > JTEFUN > If you mean that if a user types a lot of data into the one cell and that when they reach 7 characters the rest of the data is automatically inserted into the next cell, then I don't think you can do that....

Counting # of events per day`
I have a file with the following format col a event name col b event date (e.g., 9/01/2006) col c event day of week (Mon, Tu, etc) col d, etc other info There are about 1600 of them. I need to get the following info: Number of days throughout the year with 0, 1, 2, 3, or 4 or more events per day Similar results for Mondays, Tuesdays, etc. Any suggestions on how to go about doing that? Thanks Hi Elliott Create a Pivot Table. Data>Pivot Table>Next>Mark the range of your data>Next>Layout Drag Event day of Week to Column Area Drag Event Date to Row area Drag Event Name to...

limit create window/control max count?
Hi all. I am testing a create control. But I want create many control (more 10000) in dialog. I test dynamic create CButton. It's created about 9800. I want work. 1. have a two dialog.(A, B) 2. A dialog has 40 controls(Button, Spin, static). 3. B dialog has contain A dialog. A dialog is more than 200. 4. create A dialog dynamic.(Using A->Create(IDD, this)) and attach B dialog. A dialog is modaless. 5. But not create about 190. dialog create function return 0. thank for read. Have a nice day. You need to redesign your system, IMO. -- - Mark Randall http://zetech.swehli.com...

BULK Conditional Formatting
I've read the posts on conditional formatting for cell colour based o another cell's value (eg. set the conditional formatting to "formul is" and then "=A1>0" and set the colour as red / blue / whatever...) however wondering if I can do this for an entire column withou individually changing the conditional formatting for each cell one b one (as there are over 400 rows). Basically I need a formula that reads the contents of column B for th particular row that is active. Can anyone help -- Rob Moyl ---------------------------------------------------------------...

Counting Texts
If cell A1 contains a paragraph of texts and if I want to count just letter "W"s (Upper or lower case), How can I achieve this? Example: A1 contains "How now brown cow" the formula should return "4". Thank you. Write a macro and use the VBA functions Instr(strName, "w") together with Split(strName, "w") and count the number of times that it finds "w" or "W" Chris "Keith" <Keith@discussions.microsoft.com> wrote in message news:CC60AA13-B569-4C55-BDC7-1B6295CDAFB9@microsoft.com... > If cell A1 co...

Counting cells with particular month and year
Thanks to help previously received, I have been using this formula to count the number of referrals received in a particular month. Is it possible to include the year as well? =SUMPRODUCT(--(MONTH(A2:A10)=Stats!A1),--(ISNUMBER(B2:B10))) Now the Year also included in the below formula the year reference cell is referred in B1 cell of STATS sheet. =SUMPRODUCT(--(MONTH(A2:A10)=Stats!A1)*--(YEAR(A2:A10)=Stats!B1),--(ISNUMBER(B2:B10))) Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Saylindara" wrote: ...

Format Problem
I have a report of a crosstab query. I formatted the left column of the report with Text Align set to Right. The report is chopping off the last character of the term, even if I set the Right Margin to 0.5". Doing so just puts space between the right edge of the border and the end of the chopped word I have played with all of the format settings in the properties with no success. If I set Text Align to Left the word is not chopped, but I would prefer Text Align set to right. Is there a way to fix this format issue with Text Align set to the Right? -- Message posted via http://www.acce...

Conditional Statements and Time Format
Hello, I have a spreadsheet where I need to calculate a range of time in a cell and display a value in another cell. Example is in cell F2 I have a time displayed of 15:34, and in the calculation window it displays as 3:34:00 PM. In Cell F3 I want to display one of three things, "1st shift", "2nd shift" or "3rd shift". Is it possible to use the conditional statements to give me the value of "1st shift" when cell F2 is between >= 07:00 and < 15:00? I can't find anything that speaks to getting thee range information from time. Thanks, Brian ...

Rich Text formatting in Access 2007
In Access 2007, is it possible to add rich text formatting to text in a memo field (or mixed formatting within any text field). I have just upgraded to the new version of Access, and it appears that this feature still has not been added. Is this correct? (If so, why?) If it is not possible to format individual words within a field in Access, , is there a simple, free add-in that will enable Access to do this kind of basic formatting, such as adding italic, bold, and underlining? Thanks very much for any information you may have. Bob Rich Text format for Memos in: Tables - Look in t...

formatting cell in vlookup
HELP I have an excel spreadsheet that is e:mailed to me by my home office. I use the vlookup feature to find the value in cell b from the information in a Proble At times when the answer is N/A and I go to the e:mailed spreadsheet to see if that number is really not there, it is in the e;mailed spreadsheet. If I type over that number in the cell on the e;mailed spreadsheet, then the formula will work in the target spreadsheet, sometimes This e;mailed spreadsheet is at a shared location on a servier, and the items I fix in the e;mailed spreadsheet on my desk top, are not available in any ...

count rows
Hi I have two worksheets - sheet1 and sheet2. Sheet1 is my main data which contains 7 colums (A-G) and number of rows varies depend on data. In sheet2 I have formula in Row1 (A1:G1) and I need to copy down the formula to exactly the same number of rows in Sheet1. (For eg: if my sheet1 has 15 rows, I need to copy the formula in A1:G1 of sheet2 till A15:G15). Hope someone help me Thanks in advance Toms --- Message posted from http://www.ExcelForum.com/ Toms You could add an unused row with a number or letter in it and then count that separately each time with countif? John "SMILE...

Conditions on form calculated textbox
Hello, In a form called Schedule (based on the table Schedule) I have the following textboxes that are bound to the table Schedule: Time In, Time Out, Time Off, Anomaly and Overtime I also have an unbound textbox called HRS that gives me the result of the following equation: =[Time Out] - [Time In] - [Time Off] - [Anomaly] + [Overtime] The above result gives me the daily hours that someone worked. The formating is decimal, as an example. 16.5 (4:30) - 7.5 (7:30) - 1.0 - 1.0+ 3.0 = 10.0 I also added another figure for lunch the hour [16.5 (4:30) - 7.5 (7:30) - 1.0 - 1.0...

Count #2
I have a schedule where I put in initials in it. Within a two week period, how do I have excell count how many times that inital appears? a b 1 bnm abc 2 dsa bnm 3 wlo dsa with the above example it would count the number of times it see certain initals. ie BNM 2 DSA 2 ABC 1 WLO 1 Thanks Hi Matt! Use: =COUNTIF($A$1:$B$3,"bnm") Or if you put the initials you want to count in a cell: =COUNTIF(A1:B3,D1) -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Argume...

Copy a formula formatted as Text In Excel
I have a UDF that returns the formula of a given cell in a text format. Is there anyway to have excel recognize that the result of the function is a formula and not text. Once I click on the cell, hit F2 to edit it and then enter, Excel realizes it's a formula and then calculates the value, Nope. The formula in the cell is going to be the UDF--not the what formula string looks like. You could add some more steps. Select the range (if more than one cell) Convert to values (edit|copy, edit|paste special|values) and finally edit|replace what: = (equal sign) with: = replace all And exce...

Conditional Concatenate
Hi all. Its been a while since I was last here. I'm wondering if its possibl to devise a formula which concatenates a sentence in column B each tim there's an occurence in column A ? Thanks T -- Message posted from http://www.ExcelForum.com One way: B1: =IF(A1<>"",A1 & " starts this sentence.","") copy down as far as necessary. In article <twaccess.16eioe@excelforum-nospam.com>, twaccess <<twaccess.16eioe@excelforum-nospam.com>> wrote: > I'm wondering if its possible > to devise a formula which concaten...

Conditional Signatures
Is there some way to set conditional signatures? eg. If I am sending a message to someone within my company (on my exchange server) it will use a short signature but if I send a message to someone outside my company it uses another longer signature? ...

Custom or conditional format to highlight today's date?
Using Excel 2002, I have all 365 days of the year listed in column B, formatted as "03/14/01". When i open the workbook, I would like the current date to be bold and red. Can I do this with a conditional format? I have tried =Today with no success. Thanks Tonso "Tonso" <wthomasss@hotmail.com> wrote in message news:13885af2-777c-43ff-9684-1b5bda8d95d6@o6g2000yql.googlegroups.com... > Using Excel 2002, I have all 365 days of the year listed in column B, > formatted as "03/14/01". When i open the workbook, I would like the > current date to b...

Formatting and loss of emails/contacts/etc?
Hi folks, Have to format and reinstall everything ... XP PRO/OFFICE 02 PRO ... I have emails/contacts/etc that I would like to be able take with me when I reinstall ... I can save copies of Excel/Word files to another hardrive. Is there a painless way of saving Outlook email/contact/etc files? Thanks in advance, Debbie The file you need to back up is your Personal Folders file (*.pst) It's where all the mail, calendar, contacts etc are stored. Just copy and reuse this file after you reformat. Take a look at these pages for info on Outlook data backup: http://www.slipstick.com/c...

Font Format
I have merged two different sets of data where one sheet has names in all caps and the other not in all caps. When I'm doing a pivot table, it is using the names as two different values. There is no option in the font format to make the non-cap names into all caps. How can I make the font all consistent so that it is not appearing as two different values. Thanks! "mira" <mira@discussions.microsoft.com> wrote in message news:6A30BAA4-8640-47C2-8B7F-9B777A6A66D6@microsoft.com... >I have merged two different sets of data where one sheet has names in all > caps...

Formatting Numbers to Text or??!!!
I have a spreadsheet that contains the following data: Date Account ID 5/14/08 5280-3-01 5/14/08 5280-3-01 5/14/08 5280-3-01 5/14/08 5280-3-01 5/14/08 5280-3-01 5/14/08 5280-3-01 5/14/08 1030-0-01 5/14/08 1030-0-01 5/14/08 2101-0-01 5/14/08 2101-0-01 5/14/08 2101-0-01 5/14/08 2101-0-01 5/14/08 2101-0-01 5/14/08 2101-0-01 5/14/08 2101-0-01 5/14/08 2101-0-01 When I save it as a .csv file it looks like this: Date Account ID 5/14/2008 3/1/5280 5/14/2008 3/1/5280 5/14/2008 3/1/5280 5/14/2008 3/1/5280 5/14/2008 3/1/5280 5/14/2008 3/1/5280 5/14/2008 1030-0-01 5/14/2...

What's the best format for pictures used in Publisher?
This thread suggests PNG is the preferred format: http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.publisher.prepress&tid=a70b836f-f21b-44c1-a361-66ba89766785&cat=&lang=en&cr=US&sloc=&p=1 What's the best format? 1. TIFF 2. PNG 3. JPG Also, is it better to LINK the picture or EMBED the picture? Mr. Analogy wrote: > What's the best format? > 1. TIFF > 2. PNG > 3. JPG PNG is best for non-photographic data or data that need to be modified many times. JPEG is a good format for photographic data that is not modified...

Mail Merge Date Format Problem
Trying to merge in data from an Excel spreadsheet but when the date goes in, it is in US format rather than english format, i.e 08/31/04 rather than 31/08/04. Tried changing the format in Excel first which doesn't work and my PC is set up with UK date as standard. Any ideas how I can change it? Cheers Nick Hi Nick (anonymous@discussions.microsoft.com), in the newsgroups you posted: || Trying to merge in data from an Excel spreadsheet but when || the date goes in, it is in US format rather than english || format, i.e 08/31/04 rather than 31/08/04. Tried changing || the format in E...

cell format #9
I have a spreadsheet that is used to track cases in a legal clinic. One of the cells tends to get fairly large and it seems that once you reach a certain point, you can still add to it, but it will not display in the cell any longer. It looks as if it stops wrapping to fit the cell. When the cell is highlighted, you can see the entire text in the formula bar, but it does not appear in the cell. Is there a way to eliminate a limit on text in any given cell? Hi there's a limit of a max of 1204 characters per cell. You can extend this limit if you manually insert some linebreaks with ...