Menu
I'm using a pivot table in a Google Spreadsheet that counts the occurrences of different types of event-types on given dates. The events are listed on one sheet, with a column for "Date" and column for "Type." Based on this a Pivot Table is produced.
The trouble is that for dates when an event-type is non-existent, COUNTA returns empty. I'd rather it return 0.
The reason is for charting and statistical purposes when you create a chart from this data, it interpolates between values, ignoring empty cells. I'd prefer that it display zero on the chart on the days when these event types don't exist...
Is this possible?
308
COUNTA counts all values in a dataset, including those which appear more than once and text values (including zero-length strings and whitespace). To count unique values, use COUNTUNIQUE . To count only numeric values use COUNT .
UPD: sorry, I've misunderstood what you need. What if, on another worksheet, you write the following?
=arrayformula(IF('PivotSheetName'!A1:Z100="";0;'PivotSheetName'!A1:Z100))
(This formula makes a copy of your Pivot table on the new worksheet, replacing empty cells with 0; moreover, the control elements of the pivot table are copied to the new worksheet as well)
119
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
