[Solved] SUMIF with style or color

Discuss the spreadsheet application

[Solved] SUMIF with style or color

Postby faz0222 » Fri Aug 29, 2008 4:06 pm

I'm using color coded cells to list and sort items in many columns and rows.
I would need a function to make the SUM of all cells with a Green background (as an example).
Is there any color attribute that I can use with the SUMIF function?
Thanks in advance
Last edited by Hagar Delest on Mon Sep 01, 2008 1:38 pm, edited 1 time in total.
Reason: tagged the thread as Solved.
OOo 2.4.X on Ms Windows XP
faz0222
 
Posts: 4
Joined: Fri Aug 29, 2008 4:02 pm

Re: SUMIF with style or color

Postby jrkrideau » Fri Aug 29, 2008 4:12 pm

Have a look at Conditional Formatting in Help
LibreOffice 6.4.3.2; Ubuntu 20.04
jrkrideau
Volunteer
 
Posts: 3788
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: SUMIF with style or color

Postby acknak » Fri Aug 29, 2008 4:24 pm

What you want is not possible. There is no method for a formula to access the style properties (e.g. colors) of a cell.

I always tell people that this is a backward strategy: you should enter all your data in cells, then derive the formatting from the data, not the other way around. If you are using color as part of your data, then you should switch to encode whatever the color represents so that it is instead represented by data in a cell. Then you can use conditional formatting or STYLE to color the cells according to the data in the cells.

However, it seems that people want to do this often enough that maybe it should be supported.

If you want to give us a specific example of how you're using color, maybe we can suggest some alternative ways to organize your data.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: SUMIF with style or color

Postby faz0222 » Fri Aug 29, 2008 4:46 pm

Here is a sample table I'm using :

------- Catego1 Catego2 Catego3 ...
Item1 timing11 timing12 timing13 ...
Item2 timing21 timing22 timing23 ...
... ... ... ...

I use colors to highlight "timing in progress" and "completed timing".
I would need to have a sum for completed timing throughout the table, same thing for the in progress timing ...
Any idea how I could make it work ? :|
Thanks
OOo 2.4.X on Ms Windows XP
faz0222
 
Posts: 4
Joined: Fri Aug 29, 2008 4:02 pm

Re: SUMIF with style or color

Postby faz0222 » Fri Aug 29, 2008 4:52 pm

Actually, I could live without the "in progress" and add a column next to each timing column to mark the "completed" ones with something (0=imcomplete/1=complete).
I was looking at the Vlookup ealier but it seems to be only working on one column at a time which wont cover my entire table :cry:
Did I miss anything with Vlookup or may be is there another function more appropriate to this operation ?
Cheers
OOo 2.4.X on Ms Windows XP
faz0222
 
Posts: 4
Joined: Fri Aug 29, 2008 4:02 pm

Re: SUMIF with style or color

Postby Dave » Fri Aug 29, 2008 5:48 pm

This has really been discussed before.

The cells must be colour-coded according to some criterion. Let that same criterion be the one used in the SUMIF() function, not the resulting colour.

David.
Dave
 
Posts: 1011
Joined: Sun Dec 23, 2007 6:53 pm

Re: SUMIF with style or color

Postby jrkrideau » Fri Aug 29, 2008 6:15 pm

faz0222 wrote:Here is a sample table I'm using :

------- Catego1 Catego2 Catego3 ...
Item1 timing11 timing12 timing13 ...
Item2 timing21 timing22 timing23 ...
... ... ... ...

I use colors to highlight "timing in progress" and "completed timing".
I would need to have a sum for completed timing throughout the table, same thing for the in progress timing ...
Any idea how I could make it work ? :|
Thanks


COUNTIF()
Assume layout like this in columns A & B
Code: Select all   Expand viewCollapse view
Item   t1
cat   Completed
cat   Incomplete
dog   Completed
dog   Incomplete



Set up two cells with Completed, Incomplete in F1 and F2.

I'd use these entries to set up the conditional formatting to give you the colours you want.

Then to sum up
=COUNTIF(B2:B5;F1)

Use the same styles you used in the conditional formatting to format the sums to get the corresponding colours.
See the attached spreadsheet for an example.
Attachments
counts.ods
(7.13 KiB) Downloaded 482 times
LibreOffice 6.4.3.2; Ubuntu 20.04
jrkrideau
Volunteer
 
Posts: 3788
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: SUMIF with style or color

Postby acknak » Fri Aug 29, 2008 9:14 pm

Here's another example (sorry jrk, I didn't know you were doing one also).

I wanted to play around using a checkbox to toggle complete/incomplete status. It works, and it's nice to use, but it's rather inconvenient to set up.

Putting this together, an alternative came to mind (see "Alternate" sheet in the sample): just use two separate columns for each state. Incomplete items in one column, completed items in another. It makes dealing with the data very simple, but unfortunately there's no easy way in Calc to move a value from one cell to another--Calc always moves the formatting along with the value.
Attachments
count_form.ods
(10.83 KiB) Downloaded 721 times
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: SUMIF with style or color

Postby faz0222 » Mon Sep 01, 2008 1:02 pm

A big thank you for taking the time to create an example guys, it really helped :D
Cheers !
OOo 2.4.X on Ms Windows XP
faz0222
 
Posts: 4
Joined: Fri Aug 29, 2008 4:02 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 11 guests