Geek asks and answers

Can I set the default value to zero when using the COUNTA function in Google Sheets?

I use a pivot table in a Google spreadsheet that counts the occurrences of various types of event types on specified dates. Events are listed on one sheet with a Date column and a Type column. Based on this, a pivot table is created.

The problem is that for dates when the event type does not exist, COUNTA returns an empty value. I would prefer it to return 0.

The reason is the construction of diagrams and for statistical purposes, when you create a diagram from this data, it interpolates the values, ignoring empty cells. I would prefer it to display zero on the chart in those days when these types of events do not exist ...

Is it possible?

+6
source share
1 answer

UPD: sorry, I misunderstood what you need. What if you write the following on another sheet?

=arrayformula(IF('PivotSheetName'!A1:Z100="";0;'PivotSheetName'!A1:Z100))

(This formula makes a copy of your pivot table on a new worksheet, replacing empty cells with 0; in addition, the controls on the pivot table are also copied to a new worksheet)

+2
source

More articles:


All Articles