Apache OpenOffice Community Forum

I've got a pivot table which is using "count" to display the count of various items in a column. Where the count is 0 (zero), the table has a blank cell, instead of the number 0.

How can I change that?

The function COUNT() and COUNTA() will count the non empty cells. Type-in zeros into cells if you want to count them (if you want to add the numbers of the zeros to the result).


And there is a option named "Show zero values" in the
Tools - Options - Open/LibreOffice Calc - View menu item.
Try to check-in it, if you want show the earo values of the results of the formulas.

The numbers in the data fields show the results for combined category values in row/column fields.
If a combination of row/column values does not exist in the source table it would be wrong to show a zero. The sum/count/whatever is not zero when it is missing.

Villeroy wrote:The numbers in the data fields show the results for combined category values in row/column fields.
If a combination of row/column values does not exist in the source table it would be wrong to show a zero. The sum/count/whatever is not zero when it is missing.

Actually that is precisely the definition of zero when counting. I can understand why in some cases a blank cell might be useful, but I want 0 when the count is 0. Note that Excel has this via a pivot table preference for "Empty cells as…" (see image). Frankly I don't quite understand why the default isn't 0, but at least there's the option.

As a practical example (and the precise case I'm using it for), if you're keeping track of box scores in softball games and you have a column for outcome with values of "W", "L" and "T" (ties are allowed), you can use a pivot table to create the standings. In this case, if a team has all "W", you want the table to read 0 under "L" and "T". And that's the logical behavior. What's the count of "L" for that team? It's not undefined; it's zero.

Zizi64 wrote:The function COUNT() and COUNTA() will count the non empty cells. Type-in zeros into cells if you want to count them (if you want to add the numbers of the zeros to the result).

I understand about count and non-empty cells, but in many cases (see my reply to the other comment), you want to be able to show a zero count in a pivot table and not just have a blank cell.

Zizi64 wrote:And there is a option named "Show zero values" in the
Tools - Options - Open/LibreOffice Calc - View menu item.
Try to check-in it, if you want show the earo values of the results of the formulas.

I'm not finding this. I see "Zero values" (which I already have checked) under Preferences > LibreOffice Calc > View > Display section

Is that what you mean?

I see "Zero values" (which I already have checked) under Preferences > LibreOffice Calc > View > Display section

Is that what you mean?

Yes. I see now you are using Mac.(That op.sys has a different menu structure...)

Hi, and welcome to the forum.

eponymous wrote:I've got a pivot table which is using "count" to display the count of various items in a column. Where the count is 0 (zero), the table has a blank cell, instead of the number 0.

Type 0 in some unused cell and press Enter. Edit → Cut. Select the cells in the pivot table which contain the numeric counts. Edit → Paste Special → Selection=Numbers → Options=(none) → Operations=Add → Shift=Don't → OK.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

MrProgrammer wrote:Hi, and welcome to the forum.

eponymous wrote:I've got a pivot table which is using "count" to display the count of various items in a column. Where the count is 0 (zero), the table has a blank cell, instead of the number 0.

Type 0 in some unused cell and press Enter. Edit → Cut. Select the cells in the pivot table which contain the numeric counts. Edit → Paste Special → Selection=Numbers → Options=(none) → Operations=Add → Shift=Don't → OK.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

That's not really a solution so much as a surrender.

I think I'll file a feature request for this.

For the softball example, the selection of teams and outcomes would be static through the "life" of the list (a season). In this situation, a workaround may be to use COUNTIFS() within a predefined table structure, instead of building a dynamic summary as a pivot table. One advantage with formulas is that they are "live". You don't need to manually refresh. Getting the table sorted according to standing may constitute more work with that solution, though.

Is it possible to attach here a file with your source data, or if that is confidential, some dummy data with the same layout?

keme wrote:For the softball example, the selection of teams and outcomes would be static through the "life" of the list (a season). In this situation, a workaround may be to use COUNTIFS() within a predefined table structure, instead of building a dynamic summary as a pivot table. One advantage with formulas is that they are "live". You don't need to manually refresh. Getting the table sorted according to standing may constitute more work with that solution, though.

Is it possible to attach here a file with your source data, or if that is confidential, some dummy data with the same layout?

Here's the actual data with no team name or anything identifiable.

I get that there might be workarounds, but my broader point is that pivot tables should provide this option.

While I do agree that "missing=zero" should be available, and even that it be the default for counting, I think the static list is better for this particular use.
One additional advantage is that you can use the static list for input validation, which helps to avoid those leading/trailing spaces and other typos. See attached edit of your file.

I see what you mean, but I do more with the pivot table than I showed. Once the names are in there once, the typo problem goes away with autocomplete.