Calculate total of items in a column
Upcoming Power Excel Seminars
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Calculate total of items in a column

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    John Tyler
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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.


  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Fort McMurray, AB
    Posts
    218
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com