I have a set of data that consists of a column of cost data, and then a table of allocations across categories (i.e. column headers are different categories, table contents are % allocations across those categories). Each cost data point has an allocation across the different categories.
I am trying to dynamically isolate the total cost for each category. So far I have tried using =sumproduct(cost_column,if(category_headers=category,category_table,))
where
cost_column = column of cost data
category_headers = row at the top of the table of allocations
category_table = table of % allocations (excl. headers)
I am entering this as an array function, but it is returning a #VALUE error.
Is there anyway that I can dynamically calculate the total cost per category? Essentially I am trying to limit the category_table array to be the same size as the cost_column array.
Thank you!
EDIT:
Example image here Example
Try this non-volatile solution: =SUMPRODUCT(INDEX($D$6:$G$15,,MATCH(D$18,$D$5:$G$5,0)),INDEX($I$6:$K$15,,MATCH($C19,$I$5:$K$5,0)))
This will not recalculate at every change in any cell of the workbook, like for instance OFFSET or INDIRECT, which may slow things down.
