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

newton_iss

New Member
Joined
May 29, 2018
Messages
2
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.


BrandPackSKUSum of Volume ltSum of Bottle PriceSum of CAN priceSum of Field1Sum of Field2
BudBottle51000650000650
BudBottle31060689000650
BudBottle11120728000650
BudBottle Total31802067000650
BudKeg510200000
BudKeg310800000
BudKeg111400000
BudKeg Total32400000
BudCAN51040091520088
BudCAN31100096800088
BudCAN111600102080088
BudCAN Total33000290400088
Bud Total972020670029040021,265432129,87654321
Corona ExtraBottleDark1180767000650
Corona ExtraBottleLight1220793000650
Corona ExtraBottle Total24001560000650
Corona ExtraCANDark12000105600088
Corona ExtraCANLight12400109120088
Corona ExtraCAN Total24400214720088
Corona Extra Total484015600021472032,2314049644,36363636
Grand Total1456036270050512024,9107142934,69230769

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

Some videos you may like

Excel Facts

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

Watch MrExcel Video

Forum statistics

Threads
1,095,362
Messages
5,444,022
Members
405,260
Latest member
Khauff

This Week's Hot Topics

Top