Formula in Pivot table

gossv

Board Regular
Joined
Jul 8, 2005
Messages
141
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.

Please help. Thank you

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
Joined
Jul 8, 2005
Messages
141
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
Joined
Mar 8, 2005
Messages
564
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
Joined
Jul 8, 2005
Messages
141
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.

Thank you for your help

Ginny
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top