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....

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
----

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....

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....

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
----

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....

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

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

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
----

"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
----

Thanks for clarification, Biff. My response was based purely on the face
value of the original post. So you could well be spot on here that this
particular OP was a follow-up of the recent previous.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Biff" wrote in message
...
Hi Max!

This poster made previous posts last week that I helped with. Local
knowledge(I guess) <g

Something like this:

A due date in one cell and a name in another. If today is within 14 days
of
the due date cf the name cell to fill orange. If today is the due date cf
the name cell to fill red.

Biff

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?

You have to enable macros when you open the sample file, otherwise it won't
work, and you'll get the errors. Bob's ColorIndex Function is already
implemented in the sample.

Try it again. It should work.

You could also try saving the file first to harddisk from the link, viz.:
Right-click on "Download File" choose "Save Target As"
Then open the file from the folder you saved it to, enabling macros of
course.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Kelly Lim" wrote in message
...
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?

Hi!

Instead of counting the colored and non-colored cells, count based on your
due dates and the conditions used to impose the formatting.

If I remember correctly, the conditional formatting is based on:

If today equals the due date - red
If the due date is within 14 days of today - orange

So, count the due date cells based on those criteria.

Assume the due dates are in the range B1:B20

For: today equals the due date (red):

=COUNTIF(B1:B20,TODAY())

For: the due date is within 14 days of today (orange):

=SUMPRODUCT(--(B1:B20=TODAY()-14),--(B1:B20<=TODAY()-1))

For cells that don't meet either of the above criteria (no color): greater
than 14 days from the due date, and greater than today:

=SUMPRODUCT(--(ISNUMBER(B1:B20)),(B1:B20<TODAY()-14)+(B1:B20TODAY()))

Biff

"Kelly Lim" wrote in message
...
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?

So, I'm not sure how it went for you ?
... maybe I'll never know .. <bg
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

I think he/she has posted again in a new thread

--
Regards,

Peo Sjoblom


"Max" wrote in message
...
So, I'm not sure how it went for you ?
.. maybe I'll never know .. <bg
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"Peo Sjoblom" wrote
I think he/she has posted again in a new thread

Thanks, Peo. Yes, so it seems. And s/he seems to be getting better at
specifying the questions ! <bg cheers.

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

Hi Biff....regarding the formula that u taught me....do i need any VBA coding
to do so?
Pls reply asap..Thanks

Dear Biff, i have tried the formulas that u gave me...and finally it works
but counting the cells in red ( due date) and in orange ( 14 days within)

How bout i have other cells in other colour? like yellow, green, and cells
with no colour? are there any formula to do so which i can make it automatic
to count too? There contain no conditional formatting.....just manual fill in
colours...

Hope to hear from u real soon...
Thank You Biff....

"Kelly Lim" wrote:
.....
How bout i have other cells in other colour?
like yellow, green, and cells
with no colour?
are there any formula to do so which i can
make it automatic to count too?
There contain no conditional formatting
.....just manual fill in colours...

For the above, you need to use a UDF (VBA), for example Bob Phillips'
ColorIndex UDF. Pl re-look the many responses given earlier in this thread /
branches: the link to Bob's page, the steps to implement Bob's UDF, a nice?
sample file with the implemented UDF to boot ... in all, a complete
"starter's kit" so as to speak. I've got practically nothing left to give
you but this response .. <bg
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Hi Max....

I 've tried all those url in this threads.....Regarding the
"KellyLim_CountingColoredCells_misc.xls" when i tried to update a colour in
the "Source Range" like changing red to orange.....and in the coloumns of
couting cells....still stays the same? How can i make it to auto update when
i have changes in the "Source Range" colours?
A huge thanks.....

... How can i make it to auto update when
i have changes in the "Source Range" colours?

Think we need to add this line:
Application.Volatile
in the UDF itself
and then press F9 to recalc

Play with this revised sample file (link below)
which has the above implemented
and also includes a new "Reference Index" sheet
with the 56 default palette colors
(got this splash out with the help from the kind folks
over in excel.programming yesterday)

Calling Bob Phillips ... :
Would be grateful for your insights here ..
(Am I doing the correct things to your UDF, etc <g)

Revised sample file:
http://flypicture.com/p.cfm?id=57100
(Right-click on the link: "Download File"
at the top in the page, just above the ads)
File: 2_KellyLim_misc.xls
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Kelly Lim" wrote in message
...
Hi Max....

I 've tried all those url in this threads.....Regarding the
"KellyLim_CountingColoredCells_misc.xls" when i tried to update a colour
in
the "Source Range" like changing red to orange.....and in the coloumns of
couting cells....still stays the same? How can i make it to auto update
when
i have changes in the "Source Range" colours?
A huge thanks.....