SumProduct with different sized arrays

I want to take select records from one sheet and sum only the matching records from another sheet, but the ranges are different sizes. As an example, in Sheet1, I want to select the codes with a "Rank" of "1", and total the "Cost" of those matching codes in Sheet2.

Can anyone please assist? I need a non-VBA solution.

Use a helper column to pull the Rank from Sheet1 onto Sheet2; any Code without a Rank on Sheet1 returns an error.

Then SUMIF(rank_range, 1, cost_range)

That will sum all the costs with Rank = 1 (which implies ignoring costs without ranks).

Thanks Ben, but this is just an simple example that I attached, adding a helper column isn't a viable options in the actual example I am trying to solve as the data sets are rather large and include multiple sheets. Any other ideas without a helper column?

Ace, that worked, I will try it with my real data and see if I get the same result. Thanks!

Originally Posted by KBreeze15

Any other ideas without a helper column?

Mostly depends on whether there are other unmentioned constraints.

Are the data ranges sorted? All / some / none?

Is it possible that there are elements missing rowwise?
For example:
Range 1 = {1,2,3,4,5}
Range 2 = {1,3,5}

Do codes repeat? How should those be handled?

Ben, thanks again, Ace's solution worked, so I think I am all set. Awesome forum!

Ace, how would I sum on more than one Rank? Say, Rank=1 or Rank=2.

Ace, this works for my original sample data, but my real data isn't quite the same, my apologies. I have updated my sample spreadsheet and attached to hopefully give a more realistic representation. In the new spreadsheet, I want to sum codes with ranks 11 and 22. In addition, I do not want to count the cost twice for a code if the code happens to have a rank of 11 and 22 in Sheet1. For example, code 1003 has a rank of both 11 and 22 in Sheet1, but when I sum 1003 in Sheet2, I only want to count it once (uniquely). Hope this makes sense, I really appreciate your help.

It seems to count the first record twice? I uploaded a new file to show the desired result, which is on Sheet2.

Attached new file.

Hi,

I have solved your problem....
If you need any explanation, I will provide you...

have a look...

sorry bro,

i interpreted your thread wrongly....

but i am back with pin point solution...

you just have to enter the rank...

and it will provide the solution..

for explanation just contact me..

XOR LX's solution (post #17) solved my issue, thanks for the replies.