SumProduct

[lostinformulas]

I have made a stab at creating a SumProduct formula. However, it does not work. Help, Please.

=F5-(SUMPRODUCT(--('PO Created'!B2:B906=F5),--('PO Created'!G2:G906)))

F5 is the amount that I want to subtract from
The information that I want to use is on a separate worksheet inside the same workbook. The name of the sheet is (PO Created)
I want the formula to search column B for a match to F5, when it finds a match I want it to subtract the amount from that row in coulmn G.

Thank in advance for any and all advice.
Lostinformulas

[daddylonglegs]

Why doesn't that work, what result do you get?

If you're just expecting to find one match for F5 you're probably better of with VLOOKUP, i.e.

=F5-VLOOKUP(F5,'PO Created'!B2:G906,6,0)

[lostinformulas]

I am gettting the the same amount as what is in F5, it should be $2,500.00 dollars less. There will be multi entries that will meet the requirements. Some cells are blank, will that effect the answer?


Lostinformulas

PS: What I am trying to do, I have a purchase requistion for $500,000.00 and I am writting PO's against the PR and want to know how much is left. If you can think of another formula that will work, that would be great. I have many PR's and PO's that I need to apply this too.

[daddylonglegs]

You still haven't said why your original formula didn't work. If you want to sum all the cells in column G where column B matches F5, and subtract the result from F5 then your SUMPRODUCT formula should work, although with one condition you only need SUMIF, i.e.

=F5-SUMIF('PO Created'!B2:B906,F5,'PO Created'!G2:G906)

If that doesn't work either, then perhaps you have a data mismatch, what's in F5, are you sure it exactly matches the data in column B?

[lostinformulas]

Thank you,

You hit it on the head I was trying to match the wrong cell. I wanted it to subtract from F5 but it need to match F6.

Thank you so much, now the Sumif or the SumProduct formula will work.

When you have looked at it for a long time you can't see it anymore. A new outlook on the situtation helps.


Lostinformulas