For a given table of customers (where that table contains among others the "Country" and "City" fields):

To find out what is the number of customers from each country:

1. Open the Insert > Pivot Table dialog box.
2. Select the entire customers' table range and click the Source Range button. Alternatively you can enter that range manually and click the Source Range > Reload Fields command.
3. Choose the "Country" field as the only Row field.
4. Select "Count" as the default function on the functions list in the "Data fields" section.
5. Click a cell where the formula is to be inserted and click the Insert button.

To find out what is the number of customers from each country and how many of them come from a given city in that country:

1. Open the Insert > Pivot Table dialog box.
2. Select the entire products' table range and click the Source Range button. Alternatively you can enter that range manually and click the Source Range > Reload Fields command.
3. Choose the "Country" field as the first Row field.
4. Choose the "City" field as the second Row field.
5. Select "Count" as the default function on the functions list in the "Data fields" section.
6. Click a cell where the formula is to be inserted and click the Insert button.

---

For a given table of products (where that table contains among others the "ProductName", (order) "Date", "Qty" and "Total" fields):

To display the products sales (sales volume only) breakdown based on days:

1. Open the Insert > Pivot Table dialog box.
2. Select the entire products' table range and click the Source Range button. Alternatively you can enter that range manually and click the Source Range > Reload Fields command.
3. Choose the "ProductName" field as the only Row field.
4. Choose the "Date" database field as the only Column field.
5. Choose the "Qty" field as the only Data field and select the Sum function for it.
6. Click a cell where the formula is to be inserted and click the Insert button.

To display the products sales (sales volume and quantity) breakdown based on days:

1. Open the Insert > Pivot Table dialog box.
2. Select the entire products' table range and click the Source Range button. Alternatively you can enter that range manually and click the Source Range > Reload Fields command.
3. Choose the "ProductName" field as the only Row field.
4. Choose the "Date" field as the only Column field.
5. Choose the "Total" field as the first Data field and select the Sum function for it.
6. Choose the "Qty" field as the second Data field and select the Sum function for it.
7. Click a cell where the formula is to be inserted and click the Insert button.

Pivot formulas have the following form:

pivotData(source, rows, columns, data, functions, options [, field1, filter1, field2, filter2, ...])

The above formula creates and returns a pivot table for the data in the 'source' range.

The 'rows', 'columns' and 'data' parameters represent arrays/ranges containing the respective pivot field indices. The indices are relative to the top-left corner of the 'source' range and the numbering starts from 1.
For example: {1, 5}, {4}, {1}
If some of the indices are incorrect, pivotData returns the #VALUE! error. In GS-Calc 9.0 the 'columns' array can contain only one element. The 'data' fields can be omitted, in which case the last specified row field and the default pivot function will be used (for the last row field).

The 'functions' argument is an array of predefined functions constants/IDs associated with the specified data fields. The possible values are:

• PIVOT::Sum
• PIVOT::SumPositive
• PIVOT::SumNegative
• PIVOT::SumSquares
• PIVOT::Count
• PIVOT::CountPositive
• PIVOT::CountNegative
• PIVOT::CountZeroes
• PIVOT::Min
• PIVOT::Max

For example: {PIVOT::Sum}, {PIVOT::Sum, PIVOT::Count}

Note: You must specify either the PIVOT::ColumnGrandTotals or at least one column field.
Otherwise the function will return the #NUM! error code.

The 'options' parameter can be any sum of the following constants:

• PIVOT::ColumnGrandTotals - display column grand totals in the last column
• PIVOT::RowGrandTotals - include row grand totals in the last row
• PIVOT::SubTotals - include subtotals (for pivot tables with 2 or more row fields)
• PIVOT::ShowZeroes - show zeroes for empty data fields; without this option 'empty' subtotals will be displayed as the #N/A! error codes
• PIVOT::RepeatRowFields - if multiple row fields are specified, display all their values, even duplicated ones
• PIVOT::CaseSensitive - if filters are specified, use case sensitive comparison
• PIVOT::SortRowsDescending - present the output rows using the descending sort order
• PIVOT::SortColumnsDescending - present the output columns using the descending sort order
• PIVOT::NoSourceFieldNames - the source range contains no field names in the first row; use the 'Field n.' names instead
• "SEARCH::IgnorePunctuation (or 16384) - use the word sort order (ignoring certain punctuation marks) when sorting and filtering.
• "SEARCH::NeutralSortOrder (or 32768) - use neutral, language independent string comparison instead of the default language specific comparison when sorting and filtering.
• "SEARCH::Pattern (or 65536) - treat filters that don't start with (=,>,>=,<,<=) operator as simple (wildcard) patterns.

For example: {PIVOT::ColumnGrandTotals}, {PIVOT::ColumnGrandTotals + PIVOT::RowGrandTotals + PIVOT::SubTotals}

The optional [field, filter] pairs specify the 1-based field index and the condition. Only source data meeting all the specified conditions will be included in the pivot table. Conditions can have the following form:

• A text string beginning with the =,>,>=,<,<= operators.
• A number or a search pattern: a text string containing special characters '?' (any character) or '*' (any string, including an empty string). To search for ? or * place a tilde (~) before them.

Pivot formula examples:

=pivotData(C3:H19, {1},,, {PIVOT::Sum}, PIVOT::ColumnGrandTotals)

=pivotData(sheet1!C3:H19, {1}, {2}, {3, 4}, {PIVOT::Sum, PIVOT::Count}, PIVOT::RowGrandTotals + PIVOT::ColumnGrandTotals + PIVOT::SubTotals)

=pivotData(sheet1!B3:F8, {5, 1}, {3},, {PIVOT::Sum}, PIVOT::RowGrandTotals + PIVOT::ColumnGrandTotals + PIVOT::SubTotals, 2, "Jones", 4, ">2010-01-01")