# 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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### 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
5
Views
522
Replies
1
Views
435
Replies
1
Views
616
Replies
1
Views
510
Replies
4
Views
512

1,141,914
Messages
5,709,313
Members
421,627
Latest member
bennhrios9

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back