Sum Lookup Match

Please help ! Ok, I'm trying to populate a row of cells based off of a lookup (match) that is on a different sheet. The value I'm trying to match on the other sheet is in one column, and where there is a match, I want to pull in a value from that row where the match is, except in a different column. I'll try and explain. Sheet 1: For D1's value, lookup the state name in C1 and match it to column A in Sheet 2. Where there is a match in column A on sheet 2, pull in the value from that row under column B. There can be multiple matches from Sheert 2, so it will need to sum all of the instances where there is a match. I've attached a spreadsheet to better illustrate.

Example WB.xlsx

=SUMIF(criteria range, criteria, sum range)

=SUMIF('sheet 2'!$a$2:$a$12, $c2, 'sheet 2'!$b$2:$b$12)

Also possible with SUMPRODUCT((criteriarange=criteria)*(sum range))

=SUMPRODUCT(('Sheet 2'!$A$2:$A$12=$C2)*('Sheet 2'!$B$2:$B$12))

Yes, SUMPRODUCT and SUMIFS can do that as well.

I'm not sure what you could be doing wrong. Please examine the attachment to see what you are missing.

Awesome, glad I could help out.

If the problem is solved, please go up to Thread Tools as the top and Mark Thread as Solved, so other people can reference this post in the future.

And if you think I'm awesome, you can also click the star under my name. Then other people will think I'm awesome too.