Pivot table: "Count" function omitting/ ignoring zero values

Question

Hi!

My dataset have customer names as index rows, with column of various types of charges.

Not all charges apply in every row. Thus there are a lot of zero values in cases in which those charges did not occur. They're not entered as "no value" or "blank cells" - instead they're entered as "0".

I am using a pivot table to summarize the charges by customer.

I want to show total charge for each customer. So I use "sum" with no problem.

I also want to know the "occurance" for that type of charges:

e.g. I know there are 100 shipments to this customer over the past 12 months. I want to know how many times a "waiting charge" occured. Therefore I want to count the number of times a waiting charged occured.

But my problem is, the "count" function counts every single value in that column (including all the zeros)... ie. I get 100 for that cell. But in my case, zero means a charge was not incurred.

So how can I count all the rows that are not zero?

I tried using "calculate formula" and use a countif function, e.g. =countif(b3">0") . But it's giving me an error.

Appreciate your help!

Answer 1

Countif function requires a range in which to count like the following

=COUNTIF(B2:B169,">0")

Answer 2

Excel 2007/2010 PivotTable

Count non-zero values.

With helper column.

http://c3017412.r12.cf0.rackcdn.com/06_04_11.xlsx

If you get *.zip, don't unzip, just rename *.xlsx