# Can somebody clarify Pivot Table Calculated Fields for me

#### TomCon

##### Active Member
I thought this seemed logical/simple but it does not work as i would have expected

There is one Pivot Field that contains numeric values, both positive and negative.

Lets call this field F1. I made a Calcuated Field in the Pivot Table, lets call it F1Calc.

The formula is:
If (F1>0, 1, 0)

Now, i drag that Field to the column area, the field is "Sum of F1Calc".

In the rows are several row labels, lets say Lab1,Lab2, etc.

I expected to see, for the Lab1 Pivot Table row, the count of F1>0 for lab 1. And for the Lab2 row, the count of F1>0 for Lab2. Etc.

BUT, i do not see this.

The Pivot Table tells me that the Sum of F1Calc is exactly 1 for every row, Lab1, Lab2, etc.

Why is this? If i have a column within the Pivot Table range with the same formula, the Pivot Table will give the correct sum for each label, Lab1,Lab2, etc.

Is there a way to accomplish the above with a Pivot Table Calculated Field, or MUST i do back and alter the data range of the Pivot Table?

And if that is the case, why is a Calculated Field not acting the same as if it were a formula within the Pivot Table range? What is it that Calculated Fields are, if not that?

Thanks if you can explain this all!

Yes, i know the "workaround" is to alter the Pivot Table input range, but i am looking for how to solve this with a Calculated Field within the Pivot Table instead.

Thanks!

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Replies
2
Views
78
Replies
1
Views
144
Replies
0
Views
57
Replies
2
Views
672
Replies
8
Views
1K

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,125
Messages
5,768,250
Members
425,460
Latest member
Astros1243

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