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

Writer2xhtml

Logo for Writer2xhtml

Writer2xhtml enables you to publish your Writer documents to EPUB and HTML5. It provides an alternative to the built in filters in LibreOffice with focus on quality and flexibility.

04-2024

4228

★ ★ ★ ☆ ☆

wrap text, insert copied row or column

Logo for wrap text, insert copied row or column

toolbar: button for wrap and unwrap text, insert copied row or column and paste value in calc

 
 

Not rated yet

Validator

Validator is an LibreOffice Calc extension that validates cells based on selected rules. From Calc: Select Tools → Detective → Validator.

04-2024

113

Not rated yet

Validar CPF e CNPJ

Logo for Validar CPF e CNPJ

Extensão que implementa as funções VALIDACPF e VALIDACNPJ no LibreOffice Calc.


"Validar CPF e CNPJ" implements the VALIDACPF and VALIDACNPJ functions in LibreOffice Calc. It's developed for brazilian users.

04-2024

280

★ ★ ★ ☆ ☆

Update links (beta)

Updates all manual links to the most recently saved version of a linked file.

04-2024

74

Not rated yet

Tools For Calc Edit

Logo for Tools For Calc Edit

[En] Add options in Edit menu.
[Es] Agrega opciones al menu Editar.

04-2024

167

Not rated yet

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