Using SUMPRODUCT for multiplying cells based on multiple criteria

I am having difficulty creating a formula and I'm unsure of what function I need to use. I have a spreadsheet with a list of items, i.e. Item 1, Item 2, Item 3, etc. I want to calculate the Unit Hours*Qty by a specific Unit Hour associated with each Item, depending on which Item is listed in that row.

The formula below is the best way I can describe what I'm trying to do, I think.

I know I'm missing something yet I can't figure out what it is.

=SUMPRODUCT(a9:a13=e3,b9*d3 OR a9:a13=e4,b10*d4 OR a9:a13=e5,b11*d5 OR a9:a13=e6,b12*d6)

Both columns D & E relate to each other; their range is D3:E6, which is static. I want excel to check Column A for a matching value in the specified range in Column E, then return the value of the corresponding specified Column D value multiplied by the value in Column B.

I hope this makes sense. I have attached a screenshot of what I'm working with.
Thanks in advance for your help!!!

It seems as if a normal SUMIF() should suffice for you.

In F3, try this, then copy down:

=SUMIF(A:A, E3, B:B)

Yes, that does work. Your values in column A need to exactly match the value in E3. No hidden spaces (check for that).

If you can't find the problem, post a workbook so we can spot the problem for you. Click GO ADVANCED and use the paperclip icon to post up your workbook.

Reverse the values you have in Col D and Col E so that you have "Description" in Col D and "Unit Hr" in Col E.

Then use:

=VLOOKUP(A9,$D$3:$E$6,2,0)*B9

and drag down

We are providing answers for 2 different things here.

Mine is for Cell C9 and copied down.

JB's is for Cell F3 copied down

The other formula you need in C9 would be:

=INDEX($D$3:$D$6, MATCH($A9, $E$3:$E$6, 0)) * $B9

...copy that down.

Originally Posted by Cutter

You don't agree that switching Col D and Col E is simplest?

No. It's a great solution for fans of VLOOKUP, which I'm not. VLOOKUP requires the data to be "searched" always be on the left, an inherently inconvenient requirement depending on what piece of info you're searching for. INDEX/MATCH does not suffer that requirement, so I pretty much use it for everything.

Can you clarify please, anon, I assumed you were looking for a formula for F3:F6 is that correct?

If so are those values that you have in F3:F6 the expected results for your example?

If not can you indicate for which cells you require a formula and what the results should be in your example

Anon, note my original solution was for getting the SUMS in cells F3 downward. Sorry if I misunderstood your primary need.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.