Sumproduct with matched column in multiple arrays

Hi:

I am trying to build a model that allows for sum of multiplication across two arrays with a column match in both locations. I have the basic formula working well as follows:

=SUMPRODUCT('Unit Sales by Location'!B3:K3,'Current Cost by Location'!B3:K3)

However, I need the item information in column A to be matched in BOTH worksheets such that if the order gets changed in either of the source worksheets, the formula will sum the arrays correctly. When I manipulate the formula as such:

=SUMPRODUCT(--('Unit Sales by Location'!$A$3:$A$18=A3),'Unit Sales by Location'!$B$3:$B$18,'Current Cost by Location'!$B$3:$B$18)

I am only matching column information.

I have edited this formula to:

=SUMPRODUCT(--('Unit Sales by Location'!$A$3:$A$18=A3),'Unit Sales by Location'!$B$3:$K$18,'Current Cost by Location'!$B$3:$K$18)

and get the dreaded #Value! Any ideas how I can get the desired result?

I am attaching the work in process. The main take-away is I need to be able to compare columns B and C on the "Current to Bid 1" Worksheet. I prefer a formula solution to vba, if possible.

Many thanks,

Kevin

Maybe

=SUMPRODUCT(('Unit Sales by Location'!$A$3:$A$18=A3)*('Current Cost by Location'!$A$3:$A$18=A3)*'Unit Sales by Location'!$B$3:$K$18*'Current Cost by Location'!$B$3:$K$18)

Thank you both for your help; I think I am almost there. However, I have noticed that both these formulas only work when both arrays are in the order in column A. If I make one of them ascending and the other decending, the formulas return some weirdness (see attached). Any ideas? I have pasted the data in columns B and C as controls.

Solved. kvsrinivasamurthy, your Index Match was the key. Thanks so much to all for your help!

Folks, my challenge became a little more challenging. In short, I need to be able to match a column across many rows in two separate worksheets and multiply the variables (adding the results).
For instance, what is to total current COGS for Loc 1 for all the items in group A? I also have to assume that the order of the items could change. I need to execute this with a formula because the various "bid" worksheets will be changing.

Your help is greatly appreciated! I have been trying various index matches in sumproduct to no avail for about four hours.

Best,

Kevin

This is really close. However, when I sort the information in either source data files, we lose the integrity of the formula. I added the filter for "Grade" to the second array but this does not solve the problem. Do the data sources have to be in the same format? I am fearful that these will get messed up along the path.

Best,

Kevin

Please see attached.

The first data source is sorted descending, the second is sorted ascending.