Is it possible to count by color?

[cpc]

I create a array that consists of one single patterning with different colors, I just wonder if it is possible to use countif to count by color? thanks for reply.

[ccornell]

I'm guessing not. countif acts on cell contents not cell formats
http://wiki.services.openoffice.org/wik ... F_function
http://wiki.services.openoffice.org/wik ... _functions

You may want to try asking on the dev@api.documentation.openoffice.org mailing list. Maybe one of the developers there will have a suggestion.

[mriosv]

It's possible with SUM() function in array mode with conditions, and using the CELL() function to get the colour into, the SUM().
http://wiki.services.openoffice.org/wik ... y_formulas

[Villeroy]

You can apply colours (and any other attributes) by criteria. This is called "conditional formatting". Then use the same criteria for counting, summing, whatever.
If there are no clear criteria to use, you can set tags, apply conditional formatting by the tags and count the tagged cells.
For instance, you can use column A of some list to put an "X" and then use conditional formatting to highlight the row if it has an "X" in A and use COUNTIF to count all "X" in A. This allows for many other features, such as filter, sort.

I've written some userdefined function to extract hidden information, such as colours. It should be used to repair badly designed spreadsheets: http://user.services.openoffice.org/en/ ... =21&t=2762
Function CELL_BACKCOLOR can pull out the colour code out of a cell. This reveals another problem when hiding information in colours: There are many shades of the same colour.

[cpc]

Thanks for all the replies, but I still can not get into it, as you can see in the screenshot attached, I would like to count the number of different color respectively, how can I make it?

[Villeroy]

See attached file with conditional formattings in A1:J8. Calc is a spreadsheet and not a game editor.

[Dave]

Assuming that you used a special character, then a font colour for that character then the answer is, I think, "No.", since you are looking only for the colour aspect. That is also assuming that you used the same character for each entry, and so can not count the character used. Otherwise, there might be a way to count each character if different for each colour of data entry.

But I've been known to be wrong sometimes in this life.

However... you might try double-dipping. That is, have a separate column beside each visible column, and make an entry in that separate column for each type of character. Red could be "R", Blue "B", and so on. Then you can count each of those aspects using SUMIF(). Hide the unwanted columns. A little more work, but it will do what you need.

EDIT: You could take the second column entry from a conditional IF() statement, or a VLOOKUP column.

David.

[acknak]

Here's an approach that seems to work fairly well.

It uses two tricks to get the result:

First, it uses a custom number format to display all numbers as a circle symbol. That means that you can enter different numbers to represent different markers: 1 for blue, 3 for red, and 5 for black. That allows you to simply count the number of times a particular number occurs on the board, rather than counting colors.

Second, to get the colors, it uses a conditional format that applies a style which provides the appropriate color. Which style is applied is determined by the value in the cell, from a lookup table.

You can move pieces around by dragging the cells; holding Ctrl while dragging will copy the pieces to make new ones. The counts will update automatically.

[Villeroy]

Second, to get the colors, it uses a conditional format that applies a style which provides the appropriate color. Which style is applied is determined by the value in the cell, from a lookup table.

I can't believe what I see.
You are using the STYLE function in one conditional format to look up one of 3 styles?This combination of STYLE with conditional formatting overcomes the limitation of 3 styles in the most elegant manner.

I managed to add a 4th style "_G" with green color and I removed the ";0" from the VLOOKUP: http://user.services.openoffice.org/en/ ... hp?id=4684

Updated the article on my macro solution: http://user.services.openoffice.org/en/ ... 39&p=28159

[Dave]

Here's an approach that seems to work fairly well.

It uses two tricks to get the result:

NICE !!!!!!!!!!

Elegant! It's like watching a chess game, and thinking you have it figured out, then see this incredible combination you hadn't seen coming at all.

David.

[acknak]

Yes, well, that's actually a little embarrassing because, to be honest, I'm not sure that's an approved way to use STYLE. I don't think it always worked like this, but one of the developers suggested it might be one way to allow easy access to more than three styles in a conditional format. I assume that the dev went ahead and made the change at some point--I happened to be fooling around one day and found that it worked. As far as I know, it's not documented anywhere.

See Issue 71392, the comment from fst Fri Nov 10 13:34:36 2006, and the reply immediately following.

Hmm, I see now that I never added a follow-up comment on that issue--done now.

[mriosv]

it´s a very nice solution, and I have try to add two more styles and not problem, works well,

[jimavera]

WARNING: Using STYLE() in Conditional Formats (like in Game_board_example_v2.ods) causes OpenOffice suck CPU while sitting there "doing nothing". It re-evaluates the formulas over and over, many times per second. Somehow it still responds to user input, but with many such cells CPU utilization can reach 80-90% when "idle".

An ubuntu bug report is at https://bugs.launchpad.net/ubuntu/+sour ... bug/586599

(Seen in oo 3.2 OOO320m12 on amd64)

[acknak]

Yeah, I've noticed that as well: http://user.services.openoffice.org/en/ ... 58#p136558

However, I'm not even sure that using STYLE in a conditional formatting formula like this is an "approved" use.

I wonder if a report should be filed on the OOo issue tracker as well?

[achillies000]

If there are no clear criteria to use, you can set tags, apply conditional formatting by the tags and count the tagged cells.
For instance, you can use column A of some list to put an "X" and then use conditional formatting to highlight the row if it has an "X" in A and use COUNTIF to count all "X" in A. This allows for many other features, such as filter, sort.

[kq76]

I registered solely to thank you for this creative trick. I've wanted to do this for such a long time so to stumble upon it last night was a great treat. Thank you to all who contributed to it and particularly to acknack for attaching the simple to follow game board example!

[mgrivas]

As kq76, I registered also mainly to thank you a lot for your contribution to my thoughts and knowledge.
I bet you are programming in C , otherwise it's not very common to use a function in an

Code: Select all

if

clause just to trigger the function, irrelevantly of what's on the

Code: Select all

then

part.
Thank you again.

By the way, there seems to be no problem any more regarding the CPU-overloaded by re-evaluation of formulas.
I am working with LibreOffice 3.4.x on Linux (several distributions) and my spreadsheet is far more complex than the board.
I have 7 sheets, 3 of which have about 100 lines each on which I change colour to each line regarding the ROW() -for odd and even rows, the respective cell on column A (say teams) and the respective cell on column B (say groups). I have a total of 8 different colours and an exception colour (gray) which is used for empty lines and is placed as a second condition (in Java terms, the Exception), that is a total of 10 styles, including the parent-style that bears common properties.
It is interesting, though, that when I changed the styles, everything went very slow. Then I restart Calc and it goes alright, negligibly slower.
My computer is a 5-year-old notebook, with a P4 core 2 Duo and 2 GB, so it would not stand a CPU burden in today terms.