SUMPRODUCT expression for variable multipliers?

[ExcelDreamer]

Hello all,

I'm hoping one of the resident wizards can help me with this as I feel I am chasing my tail!

Here is an example of the kind of data I am working with:

Column A: Product (Product A, Product B, etc)
Column B: ProductCategory (Cat 1, Cat 2, Cat 3, etc)
Column C: RestockRate (Per Week, Per Month, Per Year, etc)
Column D: RequiredFor (Per employee, Per division, Per company, etc)
Column E: Price
Column I: RestockRateList (Per Week, Per Month, Per Year, etc)
Column J: RestockMultiplier (4.333, 1, 0.0833, etc)
Column L: RequiredForList (Per employee, Per division, Per company, etc)
Column M: RequiredForMultiplier (300, 7, 1, etc)

What I want to write is a formula that will sum (Product Price * RestockRate * RequiredFor) so I can display total required in a table by Product Category per month.

I'm thinking I should be using SUMPRODUCT and the closest thing I have found is this:

=SUMPRODUCT(SUMIF(RequiredForList,RequiredFor,RequiredForMultiplier)*(ProductCategory=CELL),Price)

For the life of me I can't think how to add in the extra multiplier for the RestockRate.

Any help will be graciously received!


-ED

[oeldere]

make a column that calculates the result.

make a column that holds the month.

after that make an pivot table of your data.

[ExcelDreamer]

Hi oeldere

Thanks for the very quick response, due to other calculations & variables a pivot table isn't the best fit for my needs. Any ideas on the formula?

Thanks,

-ED

[oeldere]

i'm intrested for the reason. (to other calculations & variables)