Dec 12, 2011

I have a data set that is coming from an Access cross-tab query, with attribute values in the first four columns and two years of sales $ in the subsequent 24 columns, much like:

Product | Category | Region | District | Jan10$ | Feb10$ | Mar10$....etc

I am summarizing the data using multiple criteria, so I've been using SUMPRODUCT formulas that utilize dynamic named ranges. All the summaries have required summing the data by individual months only, so I created only one named range for the sales data and use OFFSET to determine which column to pull it from in the SUMPRODUCT formula, such as

=SUMPRODUCT( --( rngRegion = $A$1), --(rngProduct = $A$2), OFFSET( rngSalesData, 0, $A$3))

...where A3 represent the number of the month that I'm summarizing (i.e. October = 10, etc). This method worked great, but now I'm being asked to summarize data YEAR-TO-DATE. I thought this wouldn't be difficult, as I'd just use the width part of the OFFSET function to expand the range I want to sum. But its not working. I attempted this variation, where I'm trying to sum Jan10 - Oct10.

=SUMPRODUCT( --( rngRegion = $A$1), --(rngProduct = $A$2), OFFSET( rngSalesData, , , ,$A$3)

...but it just gives me the dreaded #VALUE! error. I stripped down the formula to try to find the problem until it was just

=SUMPRODUCT( OFFSET( rngSalesData, , , ,$A$3)

And that gives me the sum I expect for the 10 months of data. The problem seems to be when I add in the additional critiera like --(rngRegion = $A$1), then the #VALUE! error pops up. So I'm guessing the issue is with the sizes of the named ranges in the formula being different even though they aren't, and they work just fine when I'm not trying to leverage the width portion of the OFFSET function. Should I try using a different technique to sum these multiple columns together?

View 5 Replies
View Related