Create a Crosstab Report with Three Simple Dynamic Array Formulas

Using Spread.NET 13 and the Dynamic Array support, it's easy to create a powerful crosstab report with three simple formulas.

This example uses the following table CrossTabData for sample data:

The first formula creates the horizontal labels across the top of the report showing a unique list of values in the Product column:

=TRANSPOSE(SORT(UNIQUE(CrossTabData[Product])))

The formula above in cell I8 creates a sorted list of the unique values from the Product column, then uses the TRANSPOSE function to make the resulting array spill to the right to the cell K8.

The second formula creates the vertical labels down the left side of the report showing a unique list of values in the Product Line column:

=SORT(UNIQUE(CrossTabData[Product Line])))

That formula is like the previous one, except that TRANSPOSE isn't needed and the values come from the Product Line column instead of the Product column.

The third and final formula creates the crosstab report data in the cell range I9:K10:

=SUMIFS(CrossTabData[Total],CrossTabData[Product Line],H9#,CrossTabData[Product],I8#)

That formula uses two dynamic array arguments, one vertical (H9#) and one horizontal (I8#), with the SUMIFS function. Normally those function arguments for SUMIFS are scalar values, so the array values cause the calculation engine to "lift" the calculation and calculate SUMIFS for each pair of values and return the result in a dynamic array, which spills in the range I9:K10.

Further Reading:

• Spread.NET 13 WinForms and Dynamic Array Functions

• Drop-down Lists and Dynamic Array Formulas in Spread.NET 13 Windows Forms