Using an Array Formula
Sometimes you have data that is already partially pivoted. For example, you might have data where you have some descriptive columns and then columns of sales data by year. If you were to use a pivot table to summarize some of the descriptive columns, you would end up having the data by year stacked—two data by year rows for each descriptive summary. When some data is stacked, it is often difficult to interpret the information in the pivot table. Even in the examples we used earlier in the chapter with units and quantity, if we summarize both fields with a pivot table, the data ends up stacked, which makes it difficult to interpret the data. If you do not want the data reported this way, you can either write a series of Union queries to un-pivot the data, or you can use the following example.
First, let's start with an example in Excel. Go back to the Excel workbook where we exported the data from the qry_BaseQuery query into an Excel workbook. Assume you want to summarize the data by CenterName and ProductName. The SumIf function does not allow you to use multiple criteria, so you have to find another way to do this. I have worked with a lot of workbooks where someone has needed to do this. They usually sort their data and then perform a SumIf function on multiple subsets of data. While this works, using the same workbook the following month or other reporting period is a challenge.
To avoid this problem, you'll want to use an array formula. It's easiest to ...