Can someone pls help me asap?
Im trying to find out if Excel can do this kind of formulas?
I have a spreadsheet with columns of names on it....with some different
colours ....
E.g. Some Name with red colour....
Some Name with peach colour....
and some Name without colour....

and then i like to know how many cells in the spreadsheet is red colour?
how many cells is in peach colour?
how many cells is without colour?

Can someone please let me know if its possible in Excel??
Thanks a lot....

Try Bob Phillips' "Processing Coloured Cells" page at:
http://www.xldynamic.com/source/xld.ColourCounter.html
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Kelly Lim" wrote in message
...
Can someone pls help me asap?
Im trying to find out if Excel can do this kind of formulas?
I have a spreadsheet with columns of names on it....with some different
colours ....
E.g. Some Name with red colour....
Some Name with peach colour....
and some Name without colour....

and then i like to know how many cells in the spreadsheet is red colour?
how many cells is in peach colour?
how many cells is without colour?

Can someone please let me know if its possible in Excel??
Thanks a lot....

err...sorry....do i have to use the VBA code in the url given? or just tried
the formulas for Excel? coz i tried..and it doesnt work....mind to give me a
step by step advice? please...
Thank you....

Yes, its VBA (UDF) and you need to implement it

Steps to implement
---------------------------
Press Alt+F11 to go to VBE

In VBE
---------
Click Insert Module
Copy paste Bob's code** in the whitespace on the right
Press Alt+Q to get back to Excel

**everything from "ColorIndex Function" till "End of ColorIndex Function"

In Excel
-----------
With a sample of colors pasted (via copy paste special formats ok)
into say E2:E5

Put in D2: =ColorIndex(E2)
Copy down to D5

D2:D5 returns the colorindexes for the corresponding cells in E2:E5

To count the no of cells within a certain source range with the color, just
use something like in H2:

=SUMPRODUCT(--(ColorIndex($B$2:$B$11)=G2))

where G2 holds the colorindex (e.g.: 3 [for red]),
and B2:B11 is the source range

(I'll try to post [a link] to a sample file a bit later ... )

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Kelly Lim" wrote in message
...
err...sorry....do i have to use the VBA code in the url given? or just
tried
the formulas for Excel? coz i tried..and it doesnt work....mind to give me
a
step by step advice? please...
Thank you....

Here's a sample file with an implementation of Bob Phillips' ColorIndex UDF:
http://flypicture.com/p.cfm?id=53910

(Right-click on the link: "Download File"
at the top in the page, just above the ads)

File: KellyLim_CountingColoredCells_misc.xls
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Hi!

The link suggested by Max isn't a good reliable solution in your case
because the cells are being colored by conditional formatting. If the cells
colors were static then it would work (sort of). Go to that site and scroll
down near the bottom of the page and you'll see the caveats.

You would have to use a formula based on the one(s) used to apply the CF'ing
to count the number of cells that meet the conditions.

Biff

"Kelly Lim" wrote in message
...
Can someone pls help me asap?
Im trying to find out if Excel can do this kind of formulas?
I have a spreadsheet with columns of names on it....with some different
colours ....
E.g. Some Name with red colour....
Some Name with peach colour....
and some Name without colour....

and then i like to know how many cells in the spreadsheet is red colour?
how many cells is in peach colour?
how many cells is without colour?

Can someone please let me know if its possible in Excel??
Thanks a lot....

"Biff" wrote:
The link suggested by Max isn't a good reliable solution in your case
because the cells are being colored by conditional formatting. ...

... and how was this* evident based on what was posted by the OP <g ?
*..cells are being colored by conditional formatting..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Ermm...guys..im lost...so in conclusion? can i use Max formula?

please let me know asap...
Thanks again...

"Kelly Lim" wrote
Ermm...guys..im lost...so in conclusion? can i use Max formula?
please let me know asap...

I don't know ?! I'm now as confused as you are <bg

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Btw Max....i don get the meaning in he

In Excel
-----------
With a sample of colors pasted (via copy paste special formats ok)
into say E2:E5


could you help to explain what do u mean by this part? mayb because this
part i don understand.....which i don get any results at the end of the
steps....

Thanks

"Kelly Lim" wrote:

In Excel
-----------
With a sample of colors pasted
(via copy paste special formats ok)
into say E2:E5

The above simply describes how to copy just the cell formats from a range of
cells to another. Cell formats will include fill colors (that's what I
thought our focus was here wrt your original post), font colors, etc. Note
that this would also implicitly copy over any conditional formatting present
in the source range. I'm mentioning this in view of Biff's clarification
that your current post could / may probably be a continuation of your
previous. Actually, I don't know. My responses were based solely on the face
value of what you described in your post where there was no hint / mention
that the fill colors were due to conditional formatting.

... which i don get any results at the end of the steps....

Were you able to download the sample file via the link I provided in the
other branch of this thread, and get it up working ?
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Hi Max, ....i download and open the url that u gave me....but y does the
whole column of

(1) Extracting ColorIndexes from E2:E5
(3) Count No of cells within the source range with the color

becomes #NAME?
#NAME?

The BEST answer is to NOT have the application differentiate soley on color..
If it's significant enough that there are meaningful colors assigned to the
cells, than it's meaningful enough to merit another column storing an actual
piece of data. you can still have your conditional formatting, BUT it's much
easier down the road if you actually have a flag designating that row's
characteristic (ie low, medium, high, etc)

** And I have had to make code work off of a background color... good rule
of thumb, NEVER have coding logic/formulas operate off of aspects of the
interface.. What are you going to do if you want to move this to a database
someday?

"Kelly Lim" wrote:

Can someone pls help me asap?
Im trying to find out if Excel can do this kind of formulas?
I have a spreadsheet with columns of names on it....with some different
colours ....
E.g. Some Name with red colour....
Some Name with peach colour....
and some Name without colour....

and then i like to know how many cells in the spreadsheet is red colour?
how many cells is in peach colour?
how many cells is without colour?

Can someone please let me know if its possible in Excel??
Thanks a lot....

No. The best answer is "Google on 'Excel count colors'".

Alan Beban

TomHinkle wrote:
The BEST answer is to NOT have the application differentiate soley on color..
If it's significant enough that there are meaningful colors assigned to the
cells, than it's meaningful enough to merit another column storing an actual
piece of data. you can still have your conditional formatting, BUT it's much
easier down the road if you actually have a flag designating that row's
characteristic (ie low, medium, high, etc)

** And I have had to make code work off of a background color... good rule
of thumb, NEVER have coding logic/formulas operate off of aspects of the
interface.. What are you going to do if you want to move this to a database
someday?

"Kelly Lim" wrote:


Can someone pls help me asap?
Im trying to find out if Excel can do this kind of formulas?
I have a spreadsheet with columns of names on it....with some different
colours ....
E.g. Some Name with red colour....
Some Name with peach colour....
and some Name without colour....

and then i like to know how many cells in the spreadsheet is red colour?
how many cells is in peach colour?
how many cells is without colour?

Can someone please let me know if its possible in Excel??
Thanks a lot....

Well that was certainly rude...

I think any object oriented programming book you read will tell you that you
shouldn't drive business processes with elements of the UI...

Also

Best is subjective.. don't simply reply to be smart. In my opinion my
reply was the best and I have HAD to write code to filter on cell colors.. in
my experience it leads to more problems than benefits.

Good day

"Alan Beban" wrote:

No. The best answer is "Google on 'Excel count colors'".

Alan Beban

TomHinkle wrote:
The BEST answer is to NOT have the application differentiate soley on color..
If it's significant enough that there are meaningful colors assigned to the
cells, than it's meaningful enough to merit another column storing an actual
piece of data. you can still have your conditional formatting, BUT it's much
easier down the road if you actually have a flag designating that row's
characteristic (ie low, medium, high, etc)

** And I have had to make code work off of a background color... good rule
of thumb, NEVER have coding logic/formulas operate off of aspects of the
interface.. What are you going to do if you want to move this to a database
someday?

"Kelly Lim" wrote:


Can someone pls help me asap?
Im trying to find out if Excel can do this kind of formulas?
I have a spreadsheet with columns of names on it....with some different
colours ....
E.g. Some Name with red colour....
Some Name with peach colour....
and some Name without colour....

and then i like to know how many cells in the spreadsheet is red colour?
how many cells is in peach colour?
how many cells is without colour?

Can someone please let me know if its possible in Excel??
Thanks a lot....