How to sum items in a column based on values in corresponding rows

[bbyler]

I have a purchasing spreadsheet I am working on that compares pricing for multiple items from different vendors. Column B has a the item purchase quantity, Column D has the item unit pricing from a specific vendor, and Column O determines the lowest price value from all the vendor columns and multiplies by the purchase quantity in Column B. At the bottom of column D, I need a formula that will add up the items in Column O where the vendor in Column D had the lowest price.

I have got it to work using the sumproduct formula, but it only works if the quantity is 1. Here is what I have so far:

=SUMPRODUCT(--(D3:D27=$O3:$O27),O3:O27)

That adds up all the items in column O that are the same value as column D, but again they are only the same if the quantity is 1. Somehow I need to divide the total in column O by the quantity before seeing if it is equal to the vendor unit price in column D. ie. I need the formula to add up the items in Column O, where (Column O/Column B)=Column D

Any help on this is greatly appreciated

 

[Andrew Poulsom]

Is it?

=SUMPRODUCT(--(D3:D27=(O3:O27/B3:B27)),O3:O27)

 

[bbyler]

Is it?

=SUMPRODUCT(--(D3:D27=(O3:O27/B3:B27)),O3:O27)

Thank you. I tried using that and get a #VALUE! a value used in the formula is the wrong data type

 

[Andrew Poulsom]

It worked for me. Can you post your data?

 

[bbyler]

It worked for me. Can you post your data?

I determined that it wouldn't work because I didn't have data through row 27 in the quantity and one of the vendor columns. If I put a one in all the vendor columns and filled out a vendor column completely with prices, it would not get the error. Is there a way to make so that this doesn't happen? Thanks

 

[Andrew Poulsom]

I get #DIV/0! if there are blank cells in column B. Try this formula confirmed with Ctrl+Shift+Enter:

=SUM(IF(B3:B27,IF(D3:D27=(O3:O27/B3:B27),O3:O27)))

 

[bbyler]

I get #DIV/0! if there are blank cells in column B. Try this formula confirmed with Ctrl+Shift+Enter:

=SUM(IF(B3:B27,IF(D3:D27=(O3:O27/B3:B27),O3:O27)))

Thank you. That works perfectly.