Skip to Main Content
Integrating Excel and Access
book

Integrating Excel and Access

by Michael Schmalz
November 2005
Intermediate to advanced content levelIntermediate to advanced
236 pages
6h 32m
English
O'Reilly Media, Inc.
Content preview from Integrating Excel and Access

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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Business solutions Automating Microsoft® Access with VBA

Business solutions Automating Microsoft® Access with VBA

Susan Sales Harkins, Mike Gunderloy
Microsoft® Office Excel 2003 Programming Inside Out

Microsoft® Office Excel 2003 Programming Inside Out

Curtis Frye, Wayne S. Freeze, Felicia K. Buckingham

Publisher Resources

ISBN: 0596009739Supplemental ContentErrata Page