Counting cells in Excel?

[Kelly Lim]

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

 

[Max]

Try Bob Phillips' "Processing Coloured Cells" page at:
http://www.xldynamic.com/source/xld.ColourCounter.html

 

[Kelly Lim]

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

 

[Biff]

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

 

[Max]

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

 

[Max]

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

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

 

[TomHinkle]

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?

 

[Alan Beban]

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

Alan Beban

 

[Max]

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

 

[TomHinkle]

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

 

[Biff]

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

 

[Biff]

I agree with Tom.

Since a cell color change doesn't trigger an event, using a method to do
calcs based on color aren't reliable.

I'm surprised that when these types of questions are asked that more replies
don't discourage this method. If you want to color cells then there's some
logic behind your reason to do so. Just use that logic to craft a formula
for counting the conditions that meet the logic.

Biff

 

[Kelly Lim]

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

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

 

[Kelly Lim]

Btw Max....i don get the meaning in here:

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

 

[Max]

In Excel

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 ?

 

[Max]

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>

 

[Max]

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.

 

[Kelly Lim]

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?

 

[Max]

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.

 

[Biff]

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:B20>TODAY()))

Biff