# Formula in Pivot table

#### gossv

##### Board Regular
Hi,

I would like to tailor a field in a pivot table to count all of the values that are above 0. At the moment when I use the count function it returns the total of every single entry however I want it to count just those with a value over 0.

I have been using the calculated field section but it is not working for me:

=MASF >0 and it sums rather than counts.

Ginny

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### gossv

##### Board Regular
Hi,

I haven't had a a reply on this question. I have been using this formula in a calcuated field =(ASF >0) but it does not sum up the total amount of values that are above 0.

Ie: I have 300 people but only 20 of them have an ASF above 0.00. I want it to count those 20, at th emoment it is counting 300.

Please help, pivot tables are so easy to update but sometimes they are a pain to use!

Thank you

#### mrhartley

##### Well-known Member
Hello,

Do you mean that the Grand Total of your calculated field is 300?

My example
field setting formula.xls
BCDE
12Destination CityShipmentsShipments >5
13AKL151
14PMR20
15WLG91
16Grand Total261
By Country

I have added a calculated field with formula: =IF('Shipment Count'>=5, 1, 0)

It clearly works but as you can see the Grand Total does not.

Is this the kind of thing you mean?

Regards
~Mark

#### gossv

##### Board Regular
Hi Mark,

That is what I mean but I need the grand total to sum. What I think I will do is add another column into the raw data, give each value above 5 a 1(=if(blah>5,1,0) and then in the pivot table sum that value. I didn't want to add formulas into my raw data but it looks like the only way that I can do it.

Ginny

Replies
1
Views
62
Replies
3
Views
57
Replies
1
Views
238
Replies
1
Views
180
Replies
9
Views
213