Thanks:  0
Likes:  0

1. ???

3. On 2002-03-10 03:20, Sami wrote:
i was thinkin something like Vlookuup n IF state ments!>????> :S
Thank you.
Again, you were quite close.....

what you want your stock book to do is look at how many lots of product have been sold (and sum them together), so normally we'd use =sum

but in this case you need to differentiate between each type of product - ie you have a condition, which is where we'd normally use =if

Excel has a function called =sumif which combines the two

(I don't like your stock book layout as it doesn't leave any room for amounts of items actually sold, so I'm assuming you'd start with say 1000 in the total quantity column and then deduct automatic sales in this cell linked to your sales book)

the syntax for =sumif is as follows :

=sumif(types of items, specific item, quantity)

so as with the other example you solved, if your sales book has a named range where your productnames sit and another named range where your quantities sit and on your stock book you have a list of your products sitting under your product name column you should be able to do something like this (remember your filenames again though)

=sumif(sa*****ok.xls!productname,stockbook cell that contains your first product name, sa*****ok.xls!quantity)

be careful to keep your ranges in this formula the same size

this should give you the amount of sales for each, so deduct this from your starting quantities (say 1000) :

=1000-sumif(sa*****ok.xls!productname,stockbook cell that contains your first product name, sa*****ok.xls!value)

By the way, I'm not sure if anyone's told you but if you click on the button that says Fx you can browse all excels =functions and it takes you through them step by step... very helpful

Hope this gets you started (or finished)
Chris

[ This Message was edited by: Chris Davison on 2002-03-17 10:03 ]

4. hey thanks a lot. by the way my stock workbook doesnt start with 1000 it has a user form which you add to the certain stocks with. If i use sumif will this take away from the stock once or all the time? plz could u give a a hand with the code??!?!?

5. sorry Sami, I have no idea what a userform is or what it does.

your first cell in quantity on your stock book.... what does it have in it ?

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•