LibreOffice Logo LibreOffice Logo Extensions, Documentation and Templates repository

multicount CountStyle

Two Calc functions for counting cells or cell content by cell style. Compatible with both Apache OpenOffice and LibreOffice.

★ ★ ★ ★ ★
Calc Extensions
Download latest

latest release: 2021-05-16 11:39:14

Description

COUNTSTYLE is an extension dedicated to the Calc module.

It is aimed to perform operations based on the cell formatting, such as typically (but not only) the background colour.

COUNTSTYLE example

Instructions

Download the most recent countstyle-x.x.x.oxt file and install it from Tools->Extension Manager.

Restart the program.

Countstyle adds two new functions to Calc, accessible from the function wizard: COUNTSTYLE and CELLSTYLE.

Formulas using these functions are automatically updated when a new style is applied.

Syntax

COUNTSTYLE(range, style [, function])

<range>: the referred cell range
<style>: the style to count (case sensitive)
<function>: an integer giving the operation to compute
    0 or missing argument: number of cells
    1: sum of values
    2: number of non empty cells
    3: average of values
    4: maximal value
    5: minimal value
    6: number of numeric values

CELLSTYLE(cell)

<cell>: the cell from which you want to get the style

Translations

  • fr

COMPTE.STYLE(plage, style [, fonction])

STYLE.CELLULE(cellule)

  • it

CONTA.STILE(area, stile [, funzione])

STILE.CELLA(cella)

  • ja (thanks to nogajun)

COUNTSTYLE(範囲, スタイル [, 機能])

CELLSTYLE(セル)

Tips

As Jeff pointed it out on the french forum, it is also possible to count multiple styles at once:

Count multiple styles at once example

Limitations

By design, COUNTSTYLE only counts cells formatted with the style given as argument. Direct formatting is ignored and colour names are not allowed.
COUNTSTYLE doesn't work with style applied by mean of conditional formatting: not only would this be awkward and unwiedly (there is no easy API method to retrieve such colouring), but also useless, as you just need to count cells on the same condition used for formatting.

Why counting styles rather than colours?

It's far easier for the user to define a style rather that trying to retrieve a colour reference.
And what should this reference be: the colour name?, the decimal value?, the rgb or the hexadecimal representation?

By the way, what colour should COUNTSTYLE count: the background colour?, the characters colour?, the borders one?...

Colour are not the same for everyone, and besides, colour names and values differ between OpenOffice and LibreOffice. For example, OpenOffice "red" is rgb 255,51,51, while one would expect 255,0,0!

With styles you can count coloured cells, but you can also count on any property you want: background or character colour, character weight, cell borders, font, any mix of those and so on...

Contributors: jmzambon

Homepage: https://gitlab.com/jmzambon/countstyle

Release Description Compatibility Operating Systems License Release notes Updated  
1.1 Localisation: JA 3.3 Linux, Windows, macOS AL Japanese translation thanks to nogajun. 2020-03-05 22:51:05 external link
1.1.1 Return error 502 when CELLSTYLE is used with cell range as parameter. 3.3 Linux, Windows, macOS AL 2020-03-05 22:51:04 external link
1.0 Initial release 3.3 Linux, Windows, macOS AL Translations
fr
COMPTE.STYLE(plage, style [, fonction])

STYLE.CELLULE(cellule)

it
CONTA.STILE(area, stile [, funzione])

STILE.CELLA(cella)

 
2020-03-05 22:51:04 external link

Related Extensions

VBA Expressions

Logo for VBA Expressions

VBA Expressions is a powerful string expression evaluator for LO Basic, providing the user with more than 100 mathematical, statistical, financial, date-time, logical and text manipulation functions, with support for variables and arrays.

10-2024

6147

★ ★ ★ ★ ★

Unlock The Matrix

Logo for Unlock The Matrix

Unprotects the array formula and returns it to the formula in the upper left cell.

04-2024

127

Not rated yet

MACROS EXTENSION FOR LIBREOFFICE

Logo for MACROS EXTENSION FOR LIBREOFFICE

Extension for scripting

04-2024

6007

★ ★ ★ ☆ ☆

Heimdall (goConfig) - The Configurator

Logo for Heimdall (goConfig) - The Configurator

Heimdall is an easy-to-use extension configurator. The configurator is multilingual.

04-2024

355

★ ☆ ☆ ☆ ☆

TexTra

Logo for TexTra

This extension performs translation on Writer, Calc. Impress.

04-2024

 

Not rated yet

Lox365: Modern functions for LibreOffice

Screenshot for Lox365: Modern functions for LibreOffice

Lox365 is a LibreOffice extension that adds modern spreadsheet functions like IMAGE to LibreOffice. https://github.com/goosepirate/lox365

10-2024

3942

★ ★ ★ ★ ☆

Feedback

★ ★ ★ ★ ★

Post your review

You cannot post reviews until you have logged in. Login Here.

Reviews

  • evan mcbeth Nov 6, 2023, 12:53 AM (13 months ago)

    ★ ★ ★ ★ ★

    Absolutely outstanding extension.

    100% deserving of being included as standard.

    The use of Styles is a bit tricky when combined with Conditional Formatting, but it is absolutely invaluable for advanced spreadsheets and visual enhancements.

  • Scoobyseb Nov 26, 2020, 2:28 PM (4 years ago)

    ★ ★ ★ ★ ★

    Marche tres bien sauf si les styles des cellules sont fournis par le formatage conditionnel.
    Y a t'il une astuce?

    • Jean-Marc Zambon Mar 14, 2021, 4:08 PM (4 years ago)

      Bonjour,
      Pas d'astuce, c'est volontaire. Voire "Limitations" dans la page de description ;-)

  • Davin Jul 15, 2020, 11:05 PM (4 years ago)

    ★ ★ ★ ★ ★

    Nice extension, very useful

    • jmzambon Nov 1, 2020, 6:01 PM (4 years ago)

      Thanks ;-)

RSS feed for reviews on this page | RSS feed for all reviews