Calculate total of items in a column

Jtyler · Apr 1st, 2002, 05:52 PM

I have a spreadsheet of inventory items. I need to calculate how many have been used of each item number. All of the item numbers are listed in column A. They have been sorted. Example A2 to A6 is part #1234. A7 to A12 starts a different item number and so on…There are 2000 cells used in column A but only 55 different item numbers. Is there a way to put the total used of each part number into a cell in column B. Many Thanks for any suggestions.

Duane · Apr 1st, 2002, 06:09 PM

There are a number of ways to do this.

So, you've got a list from A2:A2001. If you know the 55 item numbers already, you can list them in C2:C56. In D2, write =countif($A$2:$A$2001,C2) which is basically telling Excel to look through the range A2:A2001 and count all the occurances of whatever is in C2 (FYI - the dollar signs keep the range from "moving" - notice that, with no dollar signs around the C2, as you copy down, it changes to C3, C4, C5...etc...without the dollar signs, your range would have changed A3:A2002, A4:2003, etc...sometimes you want that, but in this case, you don't).

Then, copy C2 in C3:C56 and you'll have a count of all the instances of each part number in your list.

Now, if you don't know what the 55 item numbers are, then we have to find the unique values in A2:A2001. Advanced filter can handle that.

On the menu, click on Data|Filter|Advanced Filter; hit the radio button for "Copy to another location"; then, in the list range, click the little button to the right, which will take you back to the sheet with the data we want to filter, select the column A and click on the little button in the right again; now in the "copy to" field, again click on the little box in the corner, and when you're back on your sheet, select column C and hit the corner button again taking you back to the filter dialogue box; finally, make sure to click on the "unique records only" check box; and hit ok.

Now, in column C, Excel has skimmed through your list of 2000 entries and copied only the unique instances of each.

So, in column D2, enter your countif formula, copy down to D55, and you're done.

There may be many more wonderful ways to do this, but for now, this will work, and at least you've learned something that might come in handy in other tasks.

_________________
Regards, Duane

[ This Message was edited by: Duane on 2002-04-01 17:11 ]

[ This Message was edited by: Duane on 2002-04-01 17:11 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 1st, 2002, 05:52 PM	#1
Jtyler New Member Join Date: Mar 2002 Location: John Tyler Posts: 1	I have a spreadsheet of inventory items. I need to calculate how many have been used of each item number. All of the item numbers are listed in column A. They have been sorted. Example A2 to A6 is part #1234. A7 to A12 starts a different item number and so on…There are 2000 cells used in column A but only 55 different item numbers. Is there a way to put the total used of each part number into a cell in column B. Many Thanks for any suggestions.

Apr 1st, 2002, 06:09 PM	#2
Duane Board Regular Join Date: Mar 2002 Location: Toronto Posts: 173	There are a number of ways to do this. So, you've got a list from A2:A2001. If you know the 55 item numbers already, you can list them in C2:C56. In D2, write =countif($A$2:$A$2001,C2) which is basically telling Excel to look through the range A2:A2001 and count all the occurances of whatever is in C2 (FYI - the dollar signs keep the range from "moving" - notice that, with no dollar signs around the C2, as you copy down, it changes to C3, C4, C5...etc...without the dollar signs, your range would have changed A3:A2002, A4:2003, etc...sometimes you want that, but in this case, you don't). Then, copy C2 in C3:C56 and you'll have a count of all the instances of each part number in your list. Now, if you don't know what the 55 item numbers are, then we have to find the unique values in A2:A2001. Advanced filter can handle that. On the menu, click on Data\|Filter\|Advanced Filter; hit the radio button for "Copy to another location"; then, in the list range, click the little button to the right, which will take you back to the sheet with the data we want to filter, select the column A and click on the little button in the right again; now in the "copy to" field, again click on the little box in the corner, and when you're back on your sheet, select column C and hit the corner button again taking you back to the filter dialogue box; finally, make sure to click on the "unique records only" check box; and hit ok. Now, in column C, Excel has skimmed through your list of 2000 entries and copied only the unique instances of each. So, in column D2, enter your countif formula, copy down to D55, and you're done. There may be many more wonderful ways to do this, but for now, this will work, and at least you've learned something that might come in handy in other tasks. _________________ Regards, Duane [ This Message was edited by: Duane on 2002-04-01 17:11 ] [ This Message was edited by: Duane on 2002-04-01 17:11 ]