Formula in Pivot table

[gossv]

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

 

[gossv]

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]

Hello,

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

My example

field setting formula.xls
	B	C	D	E
12	Destination City	Shipments	Shipments >5
13	AKL	15	1
14	PMR	2	0
15	WLG	9	1
16	Grand Total	26	1
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]

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

 

[mrhartley]

Hi Ginny, I can't be much help I am afraid, here is another similar post

http://www.mrexcel.com/board2/viewtopic.php?p=1146776#1146776

Hope it helps a little.

Regards
~Mark