Is it possible to count by color?

Discuss the spreadsheet application
Post Reply
cpc
Posts: 6
Joined: Sat May 16, 2009 8:05 am

Is it possible to count by color?

Post by 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.
OOo 3.1.X on Ubuntu 8.x
User avatar
ccornell
Volunteer
Posts: 611
Joined: Sun Oct 07, 2007 7:21 am

Re: Is it possible to count by color?

Post by 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.
openSUSE 11.4, KDE4.6 with OpenOffice.org 3.3
mriosv
Volunteer
Posts: 651
Joined: Mon Mar 09, 2009 1:12 am
Location: Galiza (España)

Re: Is it possible to count by color?

Post by 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
LibreOffice 3.5.4 AOo-3.4 on Win 7 Ultimate
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Is it possible to count by color?

Post by 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
cpc
Posts: 6
Joined: Sat May 16, 2009 8:05 am

Re: Is it possible to count by color?

Post by 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?
Attachments
Screenshot.png
Screenshot.png (1.54 KiB) Viewed 42755 times
OOo 3.1.X on Ubuntu 8.x
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Is it possible to count by color?

Post by Villeroy »

See attached file with conditional formattings in A1:J8. Calc is a spreadsheet and not a game editor.
Attachments
colour_count.ods
(16.52 KiB) Downloaded 2721 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Dave
Posts: 1011
Joined: Sun Dec 23, 2007 6:53 pm

Re: Is it possible to count by color?

Post by 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.
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Is it possible to count by color?

Post by 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.
Attachments
Game_board_example_v2.ods
(12.24 KiB) Downloaded 2864 times
AOO4/LO5 • Linux • Fedora 23
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Is it possible to count by color?

Post by Villeroy »

acknak wrote: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.
:shock: I can't believe what I see. :bravo:
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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Dave
Posts: 1011
Joined: Sun Dec 23, 2007 6:53 pm

Re: Is it possible to count by color?

Post by Dave »

acknak wrote: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.
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Is it possible to count by color?

Post by 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.
AOO4/LO5 • Linux • Fedora 23
mriosv
Volunteer
Posts: 651
Joined: Mon Mar 09, 2009 1:12 am
Location: Galiza (España)

Re: Is it possible to count by color?

Post by mriosv »

it´s a very nice solution, and I have try to add two more styles and not problem, works well,
LibreOffice 3.5.4 AOo-3.4 on Win 7 Ultimate
jimavera
Posts: 3
Joined: Thu Feb 26, 2009 1:04 am

Re: Is it possible to count by color?

Post by 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)
OOo 3.0.X on Ubuntu 8.x + Windows XP
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Is it possible to count by color?

Post by 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?
AOO4/LO5 • Linux • Fedora 23
achillies000
Posts: 1
Joined: Tue Aug 02, 2011 6:34 pm

Re: Is it possible to count by color?

Post by 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.
OpenOffice 3.1
kq76
Posts: 7
Joined: Sun Feb 12, 2012 12:36 am

Re: Is it possible to count by color?

Post by 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! :)
OpenOffice 4.1.1 on Windows 10 (as of November 2015)
mgrivas
Posts: 4
Joined: Thu Mar 08, 2012 10:07 pm

Re: Is it possible to count by color?

Post by mgrivas »

As kq76, I registered also mainly to thank you a lot for your contribution to my thoughts and knowledge. :bravo: :super:
I bet you are programming in C :ugeek: , 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.
LibreOffice 3.4.x @ Linux (Mint, Ubuntu, openSuSE)
Post Reply