Background Color Cell Count.

[vlakcat]

Hi all, I'm trying to count the number of cells in a range of, say, 10 cells, which have a particular background color. For example,
if we have A1:A10 as a range and A1:A4 have a green background while the rest have 'no fill' I want the formula to return the count
of the green cells (4). Can this be done? I think this is possible in the last MS Office version which can also sort a whole 2D cell range
according to color (haven't used it though).

[acknak]

Can't be done in Calc. There is no way to query the color of a cell.

Although this could be useful sometimes, I wonder if it doesn't encourage a muddled approach to the problem.

Think again about your task: The way the data are presented should flow out of the data. All the meaningful information should be in the data.

The cell color is part of the presentation. Whatever information you are encoding in the cell color can be instead put into the cell data, then you can derive the cell color from the cell data, and you can count (or do whatever operation) based on the cell data, not the cell appearance.

E.g. perhaps you can add a column that specifies whatever property the cell color represents, then you can define conditional formatting that derives the color from that column.

[Biill]

However, a macro would do this. a search should get you some examples.
also try search in http://www.oooforum.org/forum/

[Villeroy]

Just copied some of my introspective cell functions over to this forum.
Use CELL_BACKCOLOR to extract the information out of your cells. Then remove the colors, replace the CELL_BACKCOLOR formulas with their results (paste special numbers without formulas) and apply conditional formatting.

[vlakcat]

Hi all,

Actually the background color is not based on condition but is statically set by me.
Think of it this way:

My range is a week (A1:A7) and each cell contains a date, +1 day on each next cell
(e.g. A1 - 1 Jan 08, A2 - 2 Jan 08 and so on.) but the cell color represents the
days that I used my car to go to work, green for 'yes', red for 'no'. The above is
no condition which can be extracted from any other data in my calc file and as
explained has to be manually set by me every day. Adding another column with a
boolean representation of the coloring scheme would simply defeat its purpose.

What I'm looking for and don't know if it can be expressed in OOo is something like
this (in an imaginary programing language):

if
A1.BACKCOLOR=FF0000;
then
counter1++;
else
counter2++;

Villeroy, what you suggest is probably close to what I want but can you please give
me a simple example in the form of OOo code?

[Villeroy]

Actually the background color is not based on condition but is statically set by me.

We all understand your approach. But you are working against the application. Spreadsheets work the other way round. Simply add a boolean column and apply conditional formatting. Putting a "1" into a cell is easier than formatting.

A1.BACKCOLOR=FF0000;

What if A1.BACKCOLOR=FF0001?

[FBB]

Hey there, nobody knows me here, and this is late but ... I have to disagree about the idea of putting a digit being easier than setting the cell BG color. If you are dealing with only one cell, perhaps, but if you are dealing with multiple I think it would actually be easier, and if you're color coding it would also be more intuitive, to simply set the color thanks to the handy paint bucket.

Of course using conditional formatting based on the numerical value is a perfectly acceptable alternative, it's just too bad the UI only allows 3 conditions, and that every code solution I've found to implement more conditions looks painful. Just venting - I've lost a lot of time trying to solve my own related issue and being able to count the cells that have a particular BG color would've been a handy solution.

[Dave]

The point is, I think, and as Villeroy makes the point, that some data property caused the OP to determine the background colour. Any other decisions can be made based upon that same property, and not on the cell colour.

David.

[Villeroy]

Yes, someone could use another property or a slightly different color to express the same meaning, a scenario which could not be handled well by any software. For this reason merging data (meaningful content) with formatting (appearance) is very bad practice. I wrote an extensible set of cell functions to fix spreadsheets that have been designed like this: http://user.services.openoffice.org/en/ ... =21&t=2762. Function CELL_BACKCOLOR extracts the numeric color code from another cell's back color.
Calc has a function STYLE to apply conditional formatting at arbitrary complexity and diversity. Due to the nature of functions, this option is limited to formula cells.
Calc supports more than 3 conditions in "normal" conditional formatting as well. If you are familiar with the built-in dialog and all it's implications then you can easily use another macro of mine which reads an arbitrary count of conditions from a prepared cell range rather than reading from the built-in dialog. http://user.services.openoffice.org/en/ ... =21&t=6039 with regards to the first time user mpcengineering who added some suggestions regarding the documentation http://user.services.openoffice.org/en/ ... f=9&t=7195