inventory comparison formula??
 

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

Any help greatfully received. Thanks

Willy


--
willy
------------------------------------------------------------------------
willy's Profile: http://www.excelforum.com/member.php...o&userid=32901
View this thread: http://www.excelforum.com/showthread...hreadid=528523

inventory comparison formula??
 

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" wrote in
message ...

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

Any help greatfully received. Thanks

Willy


--
willy
------------------------------------------------------------------------
willy's Profile:
http://www.excelforum.com/member.php...o&userid=32901
View this thread: http://www.excelforum.com/showthread...hreadid=528523

inventory comparison formula??
 

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


--
willy
------------------------------------------------------------------------
willy's Profile: http://www.excelforum.com/member.php...o&userid=32901
View this thread: http://www.excelforum.com/showthread...hreadid=528523

inventory comparison formula??
 

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" wrote in
message ...

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


--
willy
------------------------------------------------------------------------
willy's Profile:
http://www.excelforum.com/member.php...o&userid=32901
View this thread: http://www.excelforum.com/showthread...hreadid=528523

inventory comparison formula??
 

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

willy


--
willy
------------------------------------------------------------------------
willy's Profile: http://www.excelforum.com/member.php...o&userid=32901
View this thread: http://www.excelforum.com/showthread...hreadid=528523

inventory comparison formula??
 

Thanks for the feedback!

Andy.

"willy" wrote in
message ...

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

willy


--
willy
------------------------------------------------------------------------
willy's Profile:
http://www.excelforum.com/member.php...o&userid=32901
View this thread: http://www.excelforum.com/showthread...hreadid=528523