I had the same issue and found the answer I needed. Like the OP, I want to calculate an average -- SUM(field 1) divided by COUNT(field 2) -- but the problem with this is that there are two functions in the same formula (SUM divided by COUNT). As we have seen, using multiple functions in the same calculation produces unintended results.

The key that worked for me was to create a new field (field 3) in the raw data with a formula that assigns a 1 to items I want to count and a 0 to items I don not want to count, so the count of this column is just the sum. In this way, I convert COUNT(field 2) in the denominator to SUM(field 3). So, the result I need is now SUM divided by SUM, same function on top and bottom, which Excel can handle. And luckily for me in this situation, Excel's "infuriating manner" of calculating is exactly what I want.

As Fernando stated, the calculated field should just refer to the field itself; it shouldn't use SUM or COUNT or anything else. The function you want will be applied when you add the field to the pivot table and you choose the function you want.

I set my calculated function to be [field 1 / field 3], with an IF statement to avoid division by 0, and I used the SUM function when I put the calculated field in the pivot table. The end result is SUM(field 1) / SUM(field 3), which equals SUM(field 1) / COUNT(field 2)

Summary:

Restate your formula so that the same function is used on all fields; for example, find a way to restate an average (SUM/COUNT) to be SUM/SUM or COUNT/COUNT, etc.

Add fields to the raw data that will aid in the restated formula; for example, if your restated formula uses a SUM instead of a COUNT, create a new field in the raw data that assigns 1's and 0's so that the sum of this new field is equal to the count of the other field.

Create the calculated pivot field that uses the fields corresponding to the restated formula, including the new field you just created; do not use SUM or COUNT at this point.

Add your calculated field to the data area of the pivot table and choose the function you want; this function will be applied to each field that is referenced in the formula of the calculated field.