Dear all,

Good morning or good evening

New at this place, trying to clear my head about how to organize a quite large (for me) excel-pivot-table-sheet...

I have been trying to summarize all the materials needed for one project and I do have the following problems and datas .

Column E: Article number (number) - around 192 articles
Column D: Quantity (number) - variable each time
Columns O till AZ: Materials (number) - around 40, more to follow..

I want to create a pivot table that will "gather" and present each type of material calculating all the quantities from each article.

So far I have made the following calculations which did produce results but I'm sure that there is something wrong about them... or it will be when I will try to alter some of the initial inputs.

Solution a) Creating the "sumofproduct" in a line, each column material at the end has a total number (the actual material needed for the articles). When making the pivot table I have to use in the "values" the "MAX" of each material so I will avoid calculating was is already included in the same column (the sum is always bigger or the same as any number above it). A very big disadvantage is that I had to manually change the value on the pivot table from "count" to "Max".

Solution b) Creating around 40 equations (formulas) as new columns in the pivot table to multiply each material with each quantity and present the sum of the materials in the end of the pivot table... (that didn't work that much due to the amount of equations I had to make... ).


Since I'm on the process of including several more articles and materials, I would like to start creating "arrays" of values to overcome the problem of updating and re-inputting more values. I'm not aware how this should be made, any suggestions/opinions/remarks are more than welcomed.

Thanks in advance.

(My Excel version is 2007)