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
 

Some videos you may like

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
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,059
Messages
5,545,758
Members
410,704
Latest member
Cobber2008
Top