inventory comparison formula??

[willy]

Hi
still having trouble working this one out. Someone suggested Sumproduc
but I couldn't get a result. Problem is I'm trying to create a
inventory to compare prices from 10 different suppliers of 20
products. I want to be able to change the prices as they fluctuate an
see the total for each supplier reflect the change ie. the formul
needs to be quantity*price = total of 200 items. I don't need to se
total price for each item, which I know would make the whole thin
easier as I could create a SUM but I end up with twice as many columns

Any help greatfully received. Thanks

Will

 

[Guest]

Hi

How is your data laid out? Do you have suppliers and products and prices all
on one sheet? It sounds like a job for either a Pivot Table or maybe
SUMPRODUCT.

Andy.

 

[willy]

Hi Andy

Thanks for the reply
I have the workbook laid out as Product-Quantity-Supplier price (10
suppliers) as columns and 200 rows of products subtotaled in
departments.

produce - qty sp 1 - sp2 - sp3 etc
spoons - 5 of - 1.00 - 2.00 - 1.50
forks - 3 of - 2.00 - 3.00 - 2.00
Total - 8 - 11.00-19.00-13.50

Does this shed any light on the prob for you?

Thanks
willy

 

[Guest]

Your Total needs to be something like this:
=SUMPRODUCT(--($B$2:$B$200),--(C2:C200))
You can then copy this across the other suppliers.

Hope this helps.
Andy.

 

[willy]

Andy you're a star. It works a treat. No idea how but it does the trick
Thanks v much

will

 

[Guest]

Thanks for the feedback!

Andy.