Array, Match, and Index question

Posted by Matt Fuller on October 15, 2001 5:13 AM

I am creating a report that compares 2000 and 2001 sales. The industry I am in makes this a bit of a challenge. There are customers that purchase direct from many different manufacturers and some customers buy through distribution through many different distributors. I need to capture total sales for each direct and each distributor customer, without loosing the detail of which distributor they purchased from or which manufactures they used.

I have a customer master list with customer numbers assigned to each customer and subnumbers show the distribution channel used. Using these numbers and subnumbers I would like to match them up with the data extraction I have done for 2000 and for 2001. Is there a formula that will sum the numbers at each change in customer number/subnumber? I have started doing this with simple index and match functions, but I have to incorporate some kind of sum or dsum function, but I don't know how to write it.

Thanks for any help!

Matt

Posted by IML on October 15, 2001 6:26 AM

I just put toghether a spreadsheet similar to this using both array (control shift enter) formula and it caused exel to blow up dispite decent memory.
I changed all the formula to the sumproduct equivalent and this to failed miserable.
Finally, I created pivot tables and used the getpivotdata function with success. If you have a large amount of data, I'd focus on either a pivot table or using excels database functions. I've never used database functions, but this was my first jump into pivot tables, and they seem a lot easier than I had previously feared.

Posted by Don C on October 15, 2001 7:24 AM

If you can create a list of discrete manufacturers and distributors, so that there is a "code" for each m/d combination, SUMIF would work.

Create a column with the m/d combination (e.g. in row 2, =m2&d2 where m2 is the cell with the manufacturer's name or code and d2 is the cell with the distributor's name or code).

You can then use SUMIF to sum the values for each manufacturer/distributor combination.