I've got a tab with 32K+ lines of data in six columns. I need to be able to summarize it in a table based on two criteria (e.g. Region, Line of Business).
I am currently using 16 sumproduct formulas (=sumproduct((type=a5)*(reg=a4)*revenue)) where type is the line of business and reg is the region column, and 8 vlookups per region. Each tab will have about 8 regions and I'll have 4 tabs. As you can tell, this spreadsheet will quickly get out of hand in terms of calculating speed. I can't really use a pivot table because of: 1) formatting constraints 2) the vlookups refer to a separate tab than the main data dump.
What alternatives do I have other than array formulas to get the data in a nice little summary form? The formulas/presentation aren't very complicated, but I have yet to come up with a good alternative to the above.
Any help will be greatly appreciated.