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:

Table CrossTabData of sample data for crosstab report

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

Formula for horizontal labels in I8#


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:

Formula for vertical labels in H9#

=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:

Formula for crosstab report data in I9#

=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:

Download Now!<%/if%>

Sean Lawyer

Product Manager
comments powered by Disqus