Dear Excel Gurus
I need your help to transpose and summarize data from a matrix table into a calendarized vertical table with the Month# being the primary reference fields as shown in the 'Problem' table, please see screenshot below.
The business problem concerns a post-production business that is trying to summarize job hours based on the average number of episodes (pro-rata) it needs to schedule out and complete per month. Each quote on the matrix table, where the data source lies, represents a project that has its job hour requirements, total number of episodes and months to complete and deliver the project.
Problem:
The hard part is to assign lookup array values (Quotes) noted in cells $N$4:$N$6 to its criterion referenced from the matrix table. And then, I have to multiple the average episodes per month noted in cells O$4:O$6 by the job hours referenced from the matrix table to produce the total job hours in cells $O$11:$N$14 for Month 1, as an example.
The lookup array values in the quote section of the 'Problem table', need to be 3 rows in length as opposed 5 rows in length as shown on the matrix table. The reason being, I want to summarize the hours of only a select list of quotes (in random order) and not all of them as shown on the matrix table
The lookup array values that summarize the job hours per month in the Problem table has 5 rows and references the same fields of the matrix table's job type (1, 2, 3...etc).
I uploaded the excel worksheet, and to the right you will find the outcome or 'End Result' table. However, I do not want to use a conjoined multiple statement SUMPRODUCT() formulas to achieve the results as it's impractical. Instead, I like to use a one statement formula as shown in the 'Problem' table where the results have failed, unfortunately.
Thanks in advance
Al
Bookmarks