Hi Jake

Yes, its a real PITA that the PT won't do it correctly, but Sumproduct
isn't that horrendous once you get into it.
Named ranges of course make the whole thing more readable.
Bob Phillips has a good treatise on the subject you might want to read
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards

Roger Govier


"Jake" wrote in message
...
Thanks Roger, looks as if that will work. But I feel as if I'm back in
Lotus
1A!

I've never used sumproduct function before. I'll experiment.

Regards,
Jake

"Roger Govier" wrote:

Hi Jake

I agree with you. The PT does appear to be calculating the total AMT2
incorrectly by doing the .31*total Amount, and not summing the
individual amounts where the conditional test would have made
calculation of some of the individual AMT2's zero.

The only way I could get around it, was to not use a calculated field
in
the PT, but to have an extra calculated column in the source data.
My test data had columns starting with column A of Name, Date,
Amount,
Amount2 in row 1
Rows 2:9 carried values in columns A:C
In D2 I entered the following formula
=IF(SUMPRODUCT(--($A$2:$A$9=A2),--($C$2:$C$9=C2),$B$2:$B$9)0,
SUMPRODUCT(--($A$2:$A$9=A2),--($C$2:$C$9=C2),$B$2:$B$9)*0.31,0)
/SUMPRODUCT(--($A$2:$A$9=A2),--($C$2:$C$9=C2))
and copied down through D3:D9

I then added Amount2 to the Data area, having first deleted the
calculated field AMT2

--
Regards

Roger Govier


"Jake" wrote in message
...
I have created a calculated field in my pivot table. It works
properly.
However, the column and row totals do not give intended results
(sum
of
displayed results). Rather they use the same formula on all data in
that part
of the table. Here are the details:

Data
Table shows payments by transaction for all customers (field
name=AMT).
Customer may have + and - payments on any day. I need to show net
payments
per day by customer. I then need to calculate 31% of net payment
BUT
only if
net 0.

Pivot table: created 2-way table, calculating sum of pmts by
customer
by
day. works fine

calculated field: =if(AMT0,AMT*.31,0). works fine in body of table

totals: table does not calculate total of displayed results of
formula,
rather reruns formula on underlying data.
example: 2 customers, one has net pmts of 30, formula shows 9.3,
second has
net of -20, formula shows 0. I want total of 9.3. table shows total
of
3.1
(30-20)*.31.

Any suggestions?

Thanks,
Jake