Sum if cell style is...

Discuss the spreadsheet application

Sum if cell style is...

Postby dentopolis » Fri May 08, 2020 10:03 pm

i'd like to sum cell that have some style, for example "green". is there any visual basic code, function to try? or existing solution?
LibreOffice 6.4
dentopolis
 
Posts: 15
Joined: Mon Sep 23, 2013 2:44 pm

Re: Sum if cell style is...

Postby Zizi64 » Fri May 08, 2020 10:41 pm

Never use a formatting property as some valid spreadsheet data.

Use helper cells and some numeric or textual contents to mark the cells to summarize.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9602
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Sum if cell style is...

Postby Zizi64 » Fri May 08, 2020 10:44 pm

is there any visual basic code, function to try?

The AOO/LO has not built-in VBA feature.

The built-in StarBasic+API of the AOO/LO and the MS VBA are basically different tools.

Yes, it is possible to write a macro that will read the name of the applied Cell Style in a desired cell range, and then it summarize the numeric contents of the cells that have same Cell Style.
But it is better to follow my first suggestion in my post above.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9602
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Sum if cell style is...

Postby robleyd » Sat May 09, 2020 1:49 am

See also this discussion on the same topic.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3513
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Sum if cell style is...

Postby Villeroy » Sat May 09, 2020 2:23 am

viewtopic.php?t=42114&p=194261#p194261 has a document with some user-defined functions to help fixing broken sheets where information is hidden in formatting attributes. There is also a function CELL_STYLE to extract the style name. You can convert this information into a better solution with conditional formatting and with built-in functions such as SUMIF, SUMPRODUCT, filtering, pivot tables or whatever Calc has to offer. With info in attributes you are bare of options.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28664
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 14 guests