Pivot Table Counting

[Ripan]

I am having trouble trying to calculate fields through a pivot table.

Basically, I have a data set that looks like this:

Record Number State Value
1 CA 0
2 CA 12
3 CA 15
4 NJ 0
5 NJ 10

I want to set up a pivot table that will show 2 columns, one with th
total number of records and one with the total number of records wit
non-zero values. Both of these should be displayed by state.

The end result should be a table like this:

State Records With Non-zero value
CA 3 2
NJ 2 1

I have tried using the calculated field with the formula "Value <> 0
and that checks if the sum of the value by state is non-zero and thu
just shows a 1 for both CA and NJ.

Is there a way to do this using pivot tables or should I manipulate th
source data first so that the zeros are empty strings?

Any help is appreciated

 

[Debra Dalgleish]

You can add another column to the data table, and calculate if the value
is a non-zero. For example, with a new column ("Non-Zero") inserted
between State and Value, enter the following formula in cell C2:

=IF(D2<>0,1,0)

Copy the formula down to the last row of data.
Refresh the pivot table, then add the Non-Zero field to the data area,
as Sum of Non-Zero