Counting Cells #3

I want to be able to count the number of cells that have coloured text from a 
range of cells. Is their a way to do this, I tried "countif" but was unable 
to get it working.

Thanks
0
Missile (9)
1/20/2006 7:37:03 AM
excel.newusers 15348 articles. 2 followers. Follow

9 Replies
584 Views

Similar Articles

[PageSpeed] 14

See http://www.xldynamic.com/source/xld.ColourCounter.html for a solution.

-- 
 HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Missile" <Missile@discussions.microsoft.com> wrote in message
news:A3FCE1A8-D5FE-49CD-B7B8-8B08C493F9F2@microsoft.com...
> I want to be able to count the number of cells that have coloured text
from a
> range of cells. Is their a way to do this, I tried "countif" but was
unable
> to get it working.
>
> Thanks


0
bob.phillips1 (6510)
1/20/2006 8:33:17 AM
Thanks for the reply, unfortunatly when I try one of the solutions all I get 
is an error. i don't know why but I am unable to get it to work. Even when I 
tried to find the colorindex, that would not work.

"Bob Phillips" wrote:

> See http://www.xldynamic.com/source/xld.ColourCounter.html for a solution.
> 
> -- 
>  HTH
> 
> Bob Phillips
> 
> (remove nothere from email address if mailing direct)
> 
> "Missile" <Missile@discussions.microsoft.com> wrote in message
> news:A3FCE1A8-D5FE-49CD-B7B8-8B08C493F9F2@microsoft.com...
> > I want to be able to count the number of cells that have coloured text
> from a
> > range of cells. Is their a way to do this, I tried "countif" but was
> unable
> > to get it working.
> >
> > Thanks
> 
> 
> 
0
Missile (9)
1/20/2006 9:17:02 AM
Before the formulae in the webpage will work you need to enter the VBA code 
near the bottom of the webpage.
-- 
Gary''s Student


"Missile" wrote:

> Thanks for the reply, unfortunatly when I try one of the solutions all I get 
> is an error. i don't know why but I am unable to get it to work. Even when I 
> tried to find the colorindex, that would not work.
> 
> "Bob Phillips" wrote:
> 
> > See http://www.xldynamic.com/source/xld.ColourCounter.html for a solution.
> > 
> > -- 
> >  HTH
> > 
> > Bob Phillips
> > 
> > (remove nothere from email address if mailing direct)
> > 
> > "Missile" <Missile@discussions.microsoft.com> wrote in message
> > news:A3FCE1A8-D5FE-49CD-B7B8-8B08C493F9F2@microsoft.com...
> > > I want to be able to count the number of cells that have coloured text
> > from a
> > > range of cells. Is their a way to do this, I tried "countif" but was
> > unable
> > > to get it working.
> > >
> > > Thanks
> > 
> > 
> > 
0
GarysStudent (1572)
1/20/2006 9:46:01 AM
Ahh ok, thanks, will give it ago

"Gary''s Student" wrote:

> Before the formulae in the webpage will work you need to enter the VBA code 
> near the bottom of the webpage.
> -- 
> Gary''s Student
> 
> 
> "Missile" wrote:
> 
> > Thanks for the reply, unfortunatly when I try one of the solutions all I get 
> > is an error. i don't know why but I am unable to get it to work. Even when I 
> > tried to find the colorindex, that would not work.
> > 
> > "Bob Phillips" wrote:
> > 
> > > See http://www.xldynamic.com/source/xld.ColourCounter.html for a solution.
> > > 
> > > -- 
> > >  HTH
> > > 
> > > Bob Phillips
> > > 
> > > (remove nothere from email address if mailing direct)
> > > 
> > > "Missile" <Missile@discussions.microsoft.com> wrote in message
> > > news:A3FCE1A8-D5FE-49CD-B7B8-8B08C493F9F2@microsoft.com...
> > > > I want to be able to count the number of cells that have coloured text
> > > from a
> > > > range of cells. Is their a way to do this, I tried "countif" but was
> > > unable
> > > > to get it working.
> > > >
> > > > Thanks
> > > 
> > > 
> > > 
0
Missile (9)
1/20/2006 9:53:02 AM
Just spent the past hour or so looking at the VBA code and realised I have 
absolutly no idea what it all means and how to apply anything I need. Looks 
like I will just have to used old fashion pen and paper, lol

Thanks

"Missile" wrote:

> Ahh ok, thanks, will give it ago
> 
> "Gary''s Student" wrote:
> 
> > Before the formulae in the webpage will work you need to enter the VBA code 
> > near the bottom of the webpage.
> > -- 
> > Gary''s Student
> > 
> > 
> > "Missile" wrote:
> > 
> > > Thanks for the reply, unfortunatly when I try one of the solutions all I get 
> > > is an error. i don't know why but I am unable to get it to work. Even when I 
> > > tried to find the colorindex, that would not work.
> > > 
> > > "Bob Phillips" wrote:
> > > 
> > > > See http://www.xldynamic.com/source/xld.ColourCounter.html for a solution.
> > > > 
> > > > -- 
> > > >  HTH
> > > > 
> > > > Bob Phillips
> > > > 
> > > > (remove nothere from email address if mailing direct)
> > > > 
> > > > "Missile" <Missile@discussions.microsoft.com> wrote in message
> > > > news:A3FCE1A8-D5FE-49CD-B7B8-8B08C493F9F2@microsoft.com...
> > > > > I want to be able to count the number of cells that have coloured text
> > > > from a
> > > > > range of cells. Is their a way to do this, I tried "countif" but was
> > > > unable
> > > > > to get it working.
> > > > >
> > > > > Thanks
> > > > 
> > > > 
> > > > 
0
Missile (9)
1/20/2006 11:47:01 AM
Here's a recent* sample file (~ "starter's kit") which contains an
implementation of Bob Phillips' ColorIndex Function from his "Processing
Coloured Cells" page at:
http://www.xldynamic.com/source/xld.ColourCounter.html
and some examples on how to use the UDF in Excel
*given in response to another query

Link to the sample file (construct details inside):
http://www.savefile.com/files/3348428
CountCellsByFillColor_Using_BobPhillips_ColorIndex_UDF.xls

Hope the sample helps to get you started ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Missile" <Missile@discussions.microsoft.com> wrote in message
news:5E9D9DD4-5355-4453-AC31-F685E22B5B04@microsoft.com...
> Just spent the past hour or so looking at the VBA code and realised I have
> absolutly no idea what it all means and how to apply anything I need.
Looks
> like I will just have to used old fashion pen and paper, lol
>
> Thanks


0
demechanik (4694)
1/20/2006 11:16:23 PM
Thanks all for you replies,  low as my brain is I finally got it to work. 
Only problem is, for it to work i have to save/close and reopen the file. It 
dosent seem to update on the fly.

Missile

"Max" wrote:

> Here's a recent* sample file (~ "starter's kit") which contains an
> implementation of Bob Phillips' ColorIndex Function from his "Processing
> Coloured Cells" page at:
> http://www.xldynamic.com/source/xld.ColourCounter.html
> and some examples on how to use the UDF in Excel
> *given in response to another query
> 
> Link to the sample file (construct details inside):
> http://www.savefile.com/files/3348428
> CountCellsByFillColor_Using_BobPhillips_ColorIndex_UDF.xls
> 
> Hope the sample helps to get you started ..
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "Missile" <Missile@discussions.microsoft.com> wrote in message
> news:5E9D9DD4-5355-4453-AC31-F685E22B5B04@microsoft.com...
> > Just spent the past hour or so looking at the VBA code and realised I have
> > absolutly no idea what it all means and how to apply anything I need.
> Looks
> > like I will just have to used old fashion pen and paper, lol
> >
> > Thanks
> 
> 
> 
0
Missile (9)
1/22/2006 8:41:02 PM
tools>options>calculation set to automatic, if you are using a UDF that is 
non volatile press F9 to calculate

-- 
Regards,

Peo Sjoblom

Portland, Oregon




"Missile" <Missile@discussions.microsoft.com> wrote in message 
news:0193F09F-D7B5-44C9-A089-F70F55417E50@microsoft.com...
> Thanks all for you replies,  low as my brain is I finally got it to work.
> Only problem is, for it to work i have to save/close and reopen the file. 
> It
> dosent seem to update on the fly.
>
> Missile
>
> "Max" wrote:
>
>> Here's a recent* sample file (~ "starter's kit") which contains an
>> implementation of Bob Phillips' ColorIndex Function from his "Processing
>> Coloured Cells" page at:
>> http://www.xldynamic.com/source/xld.ColourCounter.html
>> and some examples on how to use the UDF in Excel
>> *given in response to another query
>>
>> Link to the sample file (construct details inside):
>> http://www.savefile.com/files/3348428
>> CountCellsByFillColor_Using_BobPhillips_ColorIndex_UDF.xls
>>
>> Hope the sample helps to get you started ..
>> --
>> Max
>> Singapore
>> http://savefile.com/projects/236895
>> xdemechanik
>> ---
>> "Missile" <Missile@discussions.microsoft.com> wrote in message
>> news:5E9D9DD4-5355-4453-AC31-F685E22B5B04@microsoft.com...
>> > Just spent the past hour or so looking at the VBA code and realised I 
>> > have
>> > absolutly no idea what it all means and how to apply anything I need.
>> Looks
>> > like I will just have to used old fashion pen and paper, lol
>> >
>> > Thanks
>>
>>
>> 

0
terre081 (3244)
1/22/2006 9:16:21 PM
Thanks, Peo !

Missile:  Yes, forcing recalc is one of 2 constraints highlighted by Bob
under the section "Constraints" in his "Processing Coloured Cells" page
(link provided earlier, part extract below). The other is that the UDF
(presently) does not count colours set by conditional formatting.

" .. The first shortcoming is that changing a cell's colour or the text
colour does not trigger the Excel calculate event. This is not a shortcoming
of the UDF, but of Excel's calculation event, but what this means is that
any formula that uses the Colorindex UDF to calculate a cell's colour will
not update should that cell's colour change. ... "
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


0
demechanik (4694)
1/22/2006 10:55:25 PM
Reply:

Similar Artilces:

Cond Format & helper-cell based "duplicate rec" tricked by content
Using 2003 Goal was to use Conditional Format and/or a helper-column cell to isolate duplicated records in a range. The formulas used were: Conditional Format =IF(COUNTIF(Range1, B5)>1,TRUE,FALSE) (Cell turns Yellow) Contigious cell =IF(COUNTIF(Range1,B2)>1,"Duplicate","") All of below cells do NOT have a duplicate thru 7 characters! But XL senses duplicates via both above formulas! M*D9000 M*D5000 M*D0004 M*D0035 M*D0002 Is there a "Bug" in XL that may see the second letter "*" as a wildcard OR stops the compare at "M*" ?...

Getting exact cell / Range from the pie chart
By selecting data point on the pie chart, I want to get corresponding cell/range ( e.g E6 or E6:E8). I can get the values ( ActiveChart.SeriesCollection(1).Values), but can't seem to find how to get exact cell. Will appreciate your reply. Thanks Suyog Suyog - Excel doesn't make it easy. You can get the series formula, and parse it to extract the range of interest. John Walkenbach shows how to use a class module to do just this on his web site, http://j-walk.com. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://Peltie...

Recurring bills #3
I am new to Money and am very frustrated at the moment! I am trying to get my head around recurring bills, here’s my situation: I have created my current account and populated with data automatically on line. If I make one of the transaction recurring, what is going to happen in the future, the recurring bill will be added to the register and also the transaction automatically by the on line update, because it is paid by direct debit every month I just do not get it, will money recognise the automatic transaction? I have been unable to find any satisfactory answers anywhere Any help appr...

Combining cell values
I have a list which has companyname and contracts numbers in column A & B Example Company Name Contract Number AAA 888888 BBB 888088 BBB 888333 What I could like to do is to write a formula or a macro to combine all the contracts numbers for a company into a single cell for example AAA 888888 BBB 888088, 888333 Can this be done? Thanks Pls try this formulae in column C =A1&" "&B1 Note : " " is for spacing Rajkuma -- Message...

Helix Express 3.5 (Mac) --> MS Access?
I have a relative who runs a tiny biz using Helix Express 3.5 on a Mac (www.helixtech.com). I'd like to explore the option of moving her to Access under Windows, but the database contains not only data but also forms. It's an odd database though. I can't even see how one accesses the underlying tables of data. She had the database custom developed for her many, many years ago, and only interacts with it through various forms, which enable her to enter data on customers and services, and produce custom reports, mailing labels, form letters, etc. Her needs are rather modest, but she...

How can I insert current date into Word table cell?
In Access and Excell, one can use the "Ctrl;" or "Ctrl Shift ;" to insert the current date and time into a field. how can I do the same in an MS Word table? Use a { DATE } field. Insert>Fields -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "tmullis" <tmullis@discussions.microsoft.com> wrote in message news:0793051B-A3F8-43BF-8355-1B5E3F0BF074@microsoft.com... > In Access and Excell, o...

How do I count If for these special distinctions
I need to count the number of names in a very long list complaring excel columns that are titles name and date. I would like the formula to count all DISTINCT names on EACH given date giving me a grand total of names. EXAMPLE James Smith June 12, 2004 James Smith June 12, 2004 Mike Black June 12, 2004 Karen Jones June 13, 2004 Mike Black June 13, 2004 Jane Smith June 13, 2004 Total Count: 5 I appreciate the help. Thank you. I don't quite understand your explanation of where the date comes into the picture, but t...

Lotus 1-2-3 keyboard commands ("/")
How do I configure EXCEL 2003 to accept Lotus 1-2-3 Keyboard Commands ("/") Tools>Options>Transition. Enable the Lotus features. Gord Dibben Excel MVP On Sat, 18 Sep 2004 10:25:02 -0700, CyberLogicAL <CyberLogicAL@discussions.microsoft.com> wrote: >How do I configure EXCEL 2003 to accept Lotus 1-2-3 Keyboard Commands ("/") ...

Counting cases between dates
Hi, I am using a waiting list of our clients and i would like to be able to calculate how many are on the list, from todays date, that have been waiting less than 6 weeks, 6 - 18 weeks and 18 weeks+. I would be very grateful for any help with this as it's driving me mad :( -- Many thanks, Lisa Hi Lisa Suppose you have the dates in ColB try the below 'Count of clients waiting for the last 6 weeks =COUNTIF(B:B,">" & TODAY()-(6*7)) 'Count of clients waiting for the last 6 - 18 weeks =COUNTIF(B:B,">" & TODAY()-(18*7))-COUNTIF(B:B...

Cell Format #8
Nope, didn't want to do that either (it's not actually my workbook). The best method may be to set the word wrap and then set the column height. ...

Outlook Backup #3
Outlook backup 2003 instals fine but when I run it and exit outlook I get the following message: Cannot copy Personal Folders: Configuration information could not be read from the domain controller, either beacuse the machine is unavailable, or access has been denied. Does anyone have any suggestoins? ...

Connector for MS Dynamics CRM 3.0 and MS Office Accounting 2007
Hi all, It seems that MS Dynamics CRM integration with other ERPs has been quite a talk here! We are also doing integration works for MS Dynamics CRM and MS OA2007. As we bridge the gap between the two solutions, we need CRM users, small business specialists and interested professionals who are willing to be advisors for this endeavor. Advisors will have the chance to influence the features of the software we are building and will get to evaluate pre-release versions of our application. If you are interested, you could visit our site for the advisor program: http://gurangosoft.com/advisor.as...

Calculating a value but omitting cells with empty data
I want to calculate a value based on several cells in say row 4. However, I do not want to include values in any columns that do not also have a non-missing value in, say, row3. Thus my calculated cell in, say column A, should have some syntax like: IF A3 <> MISSING THEN < do calculation of value> I hope this is clear. What is the proper syntax for the pseudocode that I have above? Thanks! Depends on what your calculation is. Are you summing, counting, multiply, etc.?Each has their own formula structure. For instance, the basic summing one is =SUMIF(3:3,"<>&...

fixing or freezing cell links
I have a multi-sheet workbook in which forumlas in one sheet link to cells on subsequent sheets. When I insert a column in one of the referred to sheets the forumlas automatically change to the "new" location (e.g. C25 become D25). Is there a way to prevent the forumlas from changing as I insert columns? Hi try =INDIRECT("'sheet1'!C25") >-----Original Message----- >I have a multi-sheet workbook in which forumlas in one sheet link to cells on subsequent sheets. When I insert a column in one of the referred to sheets the forumlas automatically change to the...

Update Rollup 3 won't install properly
Hi, After running lots of updates on SBS 2008 server, I finally was offered the Update Rollup 3 to alleviate the problem of my Win7 box not being able to connect. However, I am getting an error when trying to install the patch. After about 40%, the install fails with Code 6BA. Also, the "New Updates are available" baloon pops up in the system tray, offering to install it again. I've tried restarting the server, but can't seem to install this update. Any suggestions would be appreciated. Geordie GeordieB wrote: > Hi, > > After running lots of u...

Hide Cell Row and column number
is there a way to hide the cell info on the far left and far top of the sheet? I can make the screen full size and hide the top info, but how do i get rid of the 1,2,3,4,5,... on the left side of the screen? -- alexm999 ------------------------------------------------------------------------ alexm999's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=4918 View this thread: http://www.excelforum.com/showthread.php?threadid=489791 Click >Tools >Options and then select the View tab. De-select the "Row and Column headers" checkbox. ...

HELP! Seeking Instructions to Create Report of POS (NOT RMS) Discounts Applied #3
We need to run monthly reports to determine how many of our different Discounts are claimed/used and the proceeds not tendered. We have offered a number of discounts to customers and employees as incentives and cannot find a method to track how each discount is applied (e.g., buy one, get one free with coupon). The built-in Discounted Sales report in POS 2.0 lists only the resulting Sale Price; we seek to have the report sorted by PresetDiscount type but the Advanced Report Filter dialog does not list Discount in the Field dropdown box and we can't locate the table in which transactions...

Excel: Remove characters from cells using wildcards
Can you anyone help me with this problem please? I have a column of data like the one below and I would like to remove the the front part, (x) from the cell, but I don't want to remove the parts in bracket that come later in the cell, for example (Queensland). I tried using Replace (**), and it did remove the first set of brackets and its contents but unfortunately it also removed the second set of brackets and its contents. If it helps in the first set of bracket, (x), x is always a number . While in the second set always contains a word. Before: (1) ABC Far North (Queensland) 0630 New...

MSXML 3.0 patch 955069 breaks our application
If I apply MS08-069 KB 955069 our application will not fully function.This Server has MSXML 4.0 on it as well. How to I get our application to point to another version of MSXML. I am not a developer. However I contated some of ours and they were of no help. Any help would be greatly appreciated. Dent wrote: > If I apply MS08-069 KB 955069 our application will not fully function.This > Server has MSXML 4.0 on it as well. How to I get our application to point to > another version of MSXML. What kind of application is that? For instance in classic ASP with VBScript you would ...

Can I move comment indicator to another corner of cell?
In Excel 2002, those little triangular comment indicators display in the upper right corner of their cell. I want them in the U.L. corner instead. Can I tell Excel to do that? (There is no such option in Tools / Options / View's Comments panel.) Thanks. *** Nope. If it's really important to you, maybe you could hide the indicator and add a triangle shape over the corner you like. (I wouldn't bother doing, though.) baobob@my-deja.com wrote: > > In Excel 2002, those little triangular comment indicators display in > the upper right corner of their cell. > > I ...

COUNT multiple ranges
Hi Guys This formlua has has me stumped, what I am trying to do is... IF the data in column B equals say POP & the data in Column C equals JOP that entry receives a count (both entries must be on the same row to receive a count) example data A B C D E 1 POP JOP 2 PPP JOP 3 POP GUP 4 POP JOP 5 YUP KUP 6 POP JOP So the above table would return the count of 3 any help will be appreciated =SUMPRODUCT(--(B1:B6="POP"),--(C1:C6="JOP")) -- HTH RP ...

Dealing with blank cells
hi again - I hope it's OK to ask 2 questions on the same day! This problem is related to the one I posed earlier, but more general. Let's say a sheet has 3 columns. Columns A and B contain numbers, but there are also varying numbers of blank cells. I'm looking for a formula I can drag down in column C that sums the B value (if it exists) and the first A value encountered on the same row or above. Here is a sample. A B C 6 1 7 7 13 3 9 8 4 12 2 10...

Microsoft CRM 3.0 web service and InfoPath 2007
I have been trying to use InfoPath 2007 to call the CRM web service to create a record. It appears that you can't do this. Is there a .NET compatibility issue between these two products or some other reason this wouldn't work? The idea is to create a form that populates data into CRM and then starts a CRM workflow. Any comments from the experts? Thanks...Russ Hi, It's very well possible, however some coding involved. Use the Microsoft CRM 3.0 SDK which you can download from Microsoft. Next use Visual Studio to create a webservice which will be needed by the InfoPath form ...

Can series 1,2,3 label in data be RE labeled to different text?
I created a chart, a simple 3D chart using the chart wizard. I have 3 columns of information. I have sales, shelf utilization, and gross profit. I created this chart and i have 3 data series being presented. Not sure if anyone understands what i am trying to say? I thought saying i had a data series was the best approach to describing my problem in my chart. Well on the right vertical axis i have numbers that the wizard put in for me. They go from zero to 60. The data series is for the sales, shelf utilization, and gross profit. Then to the right of the chart i have labels that say,...

Locking Cells??
Hi I can't seem to lock a cell from editing. I am trying to lock particular cells that users can not enter data. Any help would be appreciated Regards Grah No but will try that now, thanks for the input Graham "Paul B" <newspab@surfbest.net> wrote in message news:uHLjZPdQDHA.3880@tk2msftngp13.phx.gbl... > Graham, did you protect the sheet after you locked the cell? > > -- > Paul B > Always backup your data before trying something new > Using Excel 2000 & 97 > Please post any response to the newsgroups so others can benefit from it > ** remo...