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