# Excel 2007 : Inventory calculation

1. ## Inventory calculation

hello everyone i have been trying to figure this one out for hours. i hope it can be done as it will be greatly beneficial. I have an excel spreadsheet for inventory. Column A has the unique part numbers, column b has the part numbers i gather from a hand scanner and column C has the quantity i have in stock.

Basically i want to scan my bar codes and have them entered into column b1-b500. I need a formula that can count how many times the bar codes in A1-A500 are matched in B1-B500 and then add it to the quantity which is column c so if i have the master bar code in a of 777 listed in A1 but i scanned the bar code 777 in the b column 9 times column c1 would be 9.

hopefully this is possible, i have tried countif and match to no avail.

2. ## Re: Inventory calculation

Originally Posted by lifeisboost
Basically i want to scan my bar codes and have them entered into column b1-b500. I need a formula that can count how many times the bar codes in A1-A500 are matched in B1-B500 and then add it to the quantity which is column c
If you want Col C to become Col C + Count of Part# in Col B then you will need to use VBA - Col C can not contain both constant (existing stock) and formula ("new" stock) simultaneously

If C should just be count of Col A in Col B (ie "new" stock only) then

C4: =COUNTIF(B:B,A4)
copied down

3. ## Re: Inventory calculation

oh wow thanks, i was so close from what i had i greatly appreciate it. One last question, what if my main inventory sheet is called Master same column A holds the master part numbers, but i use another sheet to scan called rcved and column A is where my bar codes are scanned into but the qty remains on my master sheet.

so we are using master sheet column a and column C but we need to compare sheet rcved column a to master a then update qty column c on the master

4. ## Re: Inventory calculation

Originally Posted by lifeisboost
we are using master sheet column a and column C but we need to compare sheet rcved column a to master a then update qty column c on the master
I'm not sure if we're ignoring the (possible) issue of circular referencing* but:

``Please Login or Register  to view this content.``
* circular given: existing = existing + new

5. ## Re: Inventory calculation

many thanks everything works perfectly. now one last possibility, instead of having excel count how many times it finds the product match in the master A column and the rcved A column. can we try the scenario below

on sheet rcved A i scan 1 bar code, then in rcved column B i enter how many i have. C on the master sheet then matches rcved A and then takes the numerical values from rcved B and enters it in master c for the match on master A?

6. ## Re: Inventory calculation

see SUMIF function in XL Help (rather than COUNTIF)

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1