1. Add or delete custom subtotal

Add or delete the custom subtotal in 1 part of the above image. Custom subtotals can be set one row or one column at a time.

2. Setting items for each custom subtotal

You can set items for each subtotal in the 2 part of the above image.

Setting	Content
Subtotal Name	Specify the name to display in the header.
Position	Set position to insert custom subtotal in order from the parent field. If the specified condition is not met, the custom subtotal will not be displayed in the pivot table. You can specify whether to insert the custom subtotal field before, after, beginning or end of the specify field.
Formula	Specify the value field (select from the drop-down list) and the formula (click the gear icon to input the formula from the dialog) where you want to insert the custom subtotal.

The formula uses the field code of the referenced field. In the pivot table, the custom subtotal set for the field dragged into the row field area will appear vertically. For example, in the custom subtotal for C16 cell, each field refers to the cell range in the red frame.

In the pivot table, the custom subtotal set for the field dragged into the column field area will appear horizontally. For example, in the custom subtotal for I4 cell, each field refers to the cell range in the red frame.

Custom Subtotal (Column Field)

You can hide the subtotals and calculate custom subtotals. The following example calculates the total amount of budget and actual of Sales Department/Marketing and also the ratio of Sales Department/Marketing in the total amount.

Settings:

Settings	Content
Field	Department
Subtotal Name	Sales Total
Position	Next of "Sales Division 2"
Formula	SUMIF(Department's field code,"Sales Division 1",Budget's field code) + SUMIF(Department's field code,"Sales Division 2",Budget's field code) SUMIF(Department's field code,"Sales Division 1",Actual's field code) + SUMIF(Department's field code,"Sales Division 2",Actual's field code)

Settings	Content
Field	Department
Subtotal Name	Sales Ratio
Position	Next of "Sales Division 2"
Formula	(SUMIF(Department's field code,"Sales Division 1",Budget's field code) + SUMIF(Department's field code,"Sales Division 2",Budget's field code)) / SUM(Budget's field code) (SUMIF(Department's field code,"Sales Division 1",Actual's field code) + SUMIF(Department's field code,"Sales Division 2",Actual's field code)) / SUM(Actual's field code)

Settings	Content
Field	Department
Subtotal Name	Marketing Total
Position	Next of "Marketing"
Formula	SUMIF(Department's field code,"Marketing",Budget's field code) SUMIF(Department's field code,"Marketing",Actual's field code)

Settings	Content
Field	Department
Subtotal Name	Marketing Ratio
Position	Next of "Marketing"
Formula	(SUMIF(Department's field code,"Marketing",Budget's field code)) / SUM(Budget's field code) (SUMIF(Department's field code,"Marketing",Actual's field code)) / SUM(Actual's field code)

Custom Subtotal (Row Field)

You can hide the subtotals and calculate custom subtotals. The following example calculates the actual results for every 3 months.

Setting:

Settings	Content
Fields	Month(Date)
Subtotal Name	QTR1 Total
Position	Next of "June"
Formula	SUMIF(Month(Date) field code,"Apr",Amount's field code) + SUMIF(Month(Date) field code,"May",Amount's field code) + SUMIF(Month(Date) field code,"Jun",Amount's field code)

Settings	Content
Field	Month(Date)
Subtotal Name	QTR2 Total
Position	Next of "September"
Formula	SUMIF(Month(Date) field code,"Jul",Amount's field code) + SUMIF(Month(Date) field code,"Aug",Amount's field code) + SUMIF(Month(Date) field code,"Sep",Amount's field code)

Count each item in the check box

If you check "Display a column for each item" in the value's field setting of the check box field, you can divide and aggregate by item. The following example shows the count of cases where each item is checked.

Setting:

Settings	Content
Field	Inspection Date
Subtotal Name	Total
Position	Next of "08/04/2021"
Formula	COUNTA(Fan Status field code)