# Formula for subtotal with conditions, in Pivot with Calculation - Fields (Pivot - Analyze - Calculation - Fields - subtotal by condition IF or SUMIF)

#### newton_iss

##### New Member
This is Pivot table. Field1 is counted as Bottle price / Volume. But Subtotal for Field 1 is wrong.

21,2654321 is wrong answer, watching data, we understand that price of Bottle per Lt is 65.
For some reason, Field 1 is taking all volume.

Perfect Formula would look like this: IFERROR('Bottle Price'/SUMIF(Pack;"Bottle";Volume);0), but it doesent work in calculated fields formela.

 Brand Pack SKU Sum of Volume lt Sum of Bottle Price Sum of CAN price Sum of Field1 Sum of Field2 Bud Bottle 5 1000 65000 0 65 0 Bud Bottle 3 1060 68900 0 65 0 Bud Bottle 1 1120 72800 0 65 0 Bud Bottle Total 3180 206700 0 65 0 Bud Keg 5 1020 0 0 0 0 Bud Keg 3 1080 0 0 0 0 Bud Keg 1 1140 0 0 0 0 Bud Keg Total 3240 0 0 0 0 Bud CAN 5 1040 0 91520 0 88 Bud CAN 3 1100 0 96800 0 88 Bud CAN 1 1160 0 102080 0 88 Bud CAN Total 3300 0 290400 0 88 Bud Total 9720 206700 290400 21,2654321 29,87654321 Corona Extra Bottle Dark 1180 76700 0 65 0 Corona Extra Bottle Light 1220 79300 0 65 0 Corona Extra Bottle Total 2400 156000 0 65 0 Corona Extra CAN Dark 1200 0 105600 0 88 Corona Extra CAN Light 1240 0 109120 0 88 Corona Extra CAN Total 2440 0 214720 0 88 Corona Extra Total 4840 156000 214720 32,23140496 44,36363636 Grand Total 14560 362700 505120 24,91071429 34,69230769

<colgroup><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

### Excel Facts

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