Array INDEX-MATCH with dynamic criteria and SUMPRODUCT?

[BombCenter]

Hey all,

I'm looking for a way to dynamically lookup markup rates from another worksheet, multiply them by the correct prices for matching products on the sales worksheet, and display it as a total markup amount beneath the subtotal. Some of the items, however, need to be excluded from the calculation based on product type. Here's some grossly oversimplified test data of what I'm trying to accomplish:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Sales Worksheet[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product Name:[/TD]
[TD]Product Type:[/TD]
[TD="align: center"]Cost:[/TD]
[TD="align: center"]Qty:[/TD]
[TD="align: center"]Price:[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Fruit[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD]Drugs[/TD]
[TD]NoMarkup[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]100[/TD]
[/TR]
[TR]
[TD]Broccoli[/TD]
[TD]Vegetable[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD]Bread[/TD]
[TD]Grain[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD]Beer[/TD]
[TD]NoMarkup[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD]Fish[/TD]
[TD]Meat[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]18[/TD]
[/TR]
[TR]
[TD]Cheese[/TD]
[TD]Dairy[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD]Cigarettes[/TD]
[TD]NoMarkup[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD]Cookies[/TD]
[TD]Delicious[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]600[/TD]
[TD="align: center"]1800[/TD]
[/TR]
[TR]
[TD="align: right"]Subtotal:[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]1946[/TD]
[/TR]
[TR]
[TD="align: right"]Markup/Margin:[/TD]
[TD](Excl. NoMarkup)[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]?[/TD]
[/TR]
[TR]
[TD="align: right"]Total:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Rates Worksheet[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product Type:[/TD]
[TD="align: center"]Markup %:[/TD]
[TD="align: center"]Tax Rate:[/TD]
[/TR]
[TR]
[TD]Fruit[/TD]
[TD="align: center"].05[/TD]
[TD="align: center"].0775[/TD]
[/TR]
[TR]
[TD]Vegetable[/TD]
[TD="align: center"].05[/TD]
[TD="align: center"].0775[/TD]
[/TR]
[TR]
[TD]Grain[/TD]
[TD="align: center"].10[/TD]
[TD="align: center"].0775[/TD]
[/TR]
[TR]
[TD]Meat[/TD]
[TD="align: center"].20[/TD]
[TD="align: center"].0775[/TD]
[/TR]
[TR]
[TD]Dairy[/TD]
[TD="align: center"].10[/TD]
[TD="align: center"].0775[/TD]
[/TR]
[TR]
[TD]Delicious[/TD]
[TD="align: center"].50[/TD]
[TD="align: center"].0775[/TD]
[/TR]
</tbody>[/TABLE]

I was thinking that I should be able to do something with SUMPRODUCT and INDEX-MATCH, but so far have had no joy.
E.g., =SUMPRODUCT(Sales!Product.Price, INDEX(Rates!Product.Markup, MATCH(Sales!Product.Type, Rates!Product.Type, 0), 1))

A similar INDEX-MATCH approach has worked thus far for calculating the final product price (with markup) on a per-line basis, but being able to calculate the aggregate amount formulaically would be immensely helpful. I'd prefer not to use helper columns, as the actual spreadsheet has 40ish Sales worksheets each with 60ish columns, and adding helper columns to each would be painstaking.

I feel like I'm part of the way there but am missing something key...

Any help would be appreciated.

Thanks!

 

[Marcelo Branco]

Maybe...

=SUMPRODUCT(Sales!Product.Price,SUMIF(Rates!Product.Type,Sales!Product.Type,Rates!Product.Markup))

M.

 

[BombCenter]

Maybe...

=SUMPRODUCT(Sales!Product.Price,SUMIF(Rates!Product.Type,Sales!Product.Type,Rates!Product.Markup))

M.

Won't this just sum all the markup percentages in Rates!Product.Markup (that match Product.Type) and multiply by Sales!Product.Price?
Does SUMIF return an array?
Either way I applied it to my worksheet(s) and no joy. =(

 

[Marcelo Branco]

The formula multiply each Price by the respective Markup in sheet Rates, and add all the results.
Is not that what you want? If not, explain better the expected outcome and how to obtain it.
About SUMIF: note that its second parameter is an array, not a single value, so it returns an array.

M.

 

[Marcelo Branco]

Using your data sample in post 1 and named ranges (Scope = Worksheet) the formula returned 904.4 for me
Isn't it the desired result?

M.

 

[BombCenter]

Your succinct description is absolutely what I'm looking for.
Turns out I wasn't implementing your solution correctly.
Your solution works perfectly, I accidentally reversed the Product.Type in the SUMIF clause.

Cheers! And thanks so much for all your help. I owe you a beer.

 

[Marcelo Branco]

You are very welcome. Glad to help.


M.