Calculated Fields

[Jim C.]

Is there a way to use an IF or SUMIF function in a
calculated field in a pivot table? When I try, I get all
0s as the result.

 

[Debra Dalgleish]

Can you type a small sample of the pivot table layout and data, and
explain what you'd like to calculate with a SUMIF formula?

 

[Stephen Bullen]

Hi Jim,

Is there a way to use an IF or SUMIF function in a
calculated field in a pivot table? When I try, I get all
0s as the result.

Probably not in the way you want. Calculated fields work on the result
of the other fields after consolidation, not before. In other words,
the formula is *not* applied to the individual records in the data set.

The way around it is to add extra columns to the source data.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk

 

[Jim Cline]

-----Original Message-----
Can you type a small sample of the pivot table layout and data, and
explain what you'd like to calculate with a SUMIF formula?



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.

Example -- After I bring in data into the Pivot Table, I
have two fields, Actual and Budget. I want to calculate
the % of Budget so I do the formula = Actual / Budget,
which works fine IF there is a non zero in the Budget
column. However, if the Budget column has a 0 value, the
calculated field returns #DIV/0 error. I want to put

=if(Budget=0,0,Actual/Budget)

This formula in the calculated field always returns a 0.