How to create an inventory spreadsheet

[dave_]

I am trying to create an "inventory" spreadsheet. My question is how do I set up qty count based on part number?

Let say Col A - E row 1 has the following:
part number, description, serial number, mfg, qty

If Col A has a part number listed five times, how can I show this in the qty field?
If I delete a part number based on serial number, I need the qty to update as I add and remove.

Any suggestion would help me greatly, Thanks!

-dave_

[Paul]

Hi Dave,

So if you have a part number in rows 2, 10, 14, 28 and 33, do each of them have different quantities? Or does each row represent a quantity of 1?

Are you using a separate table with unique part #'s where you want to see the total quantity of each part? Otherwise, the quantity in each of the rows above would be the same, but really make no sense.

Formula options are COUNTIF or SUMIF, but a quicker option may be a Pivot Table based on your data. You can then add Part # to the Row Header field, and Qty to the Values area and you'll get a quick sum of each part #.

Hope that helps!

[dave_]

Hi Paul,
Thanks for replying.
Each part (row) will be a qty of 1, because I have to keep count based on the serial number.
No, I am not using a different table (at least I don't think I am). Because of the serial no. the quantity will always be 1, while the produce will be more than 1.

I do not know anything about how to create a Pivot Table, could you provide a simple example?

So if I am understanding your suggestion correctly - my qty would be something like =countif(a:a, 1234partno)

col a col b col c col d col e qty total qty
part no. desc serial no. mfg serial no. (part no)
1234partno hard drive 123adv789 seagate 123ad123c 1 15

I guess I have to add a new col for total count(?) of the part no. (can be different mfg/serial no).

Again, thanks for the help.

-dave_

[Paul]

I would suggest uploading a copy of your workbook showing your current data layout, then what you would like to see added or changed (e.g. where do you want to see the totals). Show a before and after view, with the results you would expect based on the sample data. Remove any confidential information, of course.

[dave_]

Paul,
Here is a sample of what I need, hope that it is helpful. Also, any suggestions would helpful also.

Note: When a part is removed I would just delete that row that matches the description/serial no and the refresh the total count.

Thanks again,

-dave_

[dave_]

Paul,
Thanks for your help (=countif helps a lot) for my count.
Now on to my new question.