Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    No of instances in a list (Excel 2000 SP3)

    I'm sure there will be a function to achieve this, but I'm not sure which one (or the syntax!).

    I have a column which lists the references to particular items, but I need more than one line for each item.

    So I have reproduced the item no. as many times as necessary (this varies).

    My column therefore reads

    1
    1
    1
    2a
    2a
    4
    4
    4
    4

    for example.

    In this case, I have just 3 items (no.'s 1, 2a and 4), though those items "occupy" 9 rows in my spreadsheet.

    But my spreadsheet has nearly 300 items occupying c.800 lines!

    So, what function can I use to count how many different "items" are in my list?

    In my example above, I want the function to return the answer 3.

    Thanks!

    Neil

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: No of instances in a list (Excel 2000 SP3)

    Say that your list is in A1:A800. The following formula will count unique items; it is an array formula, so you must confirn it with Ctrl+Shift+Enter instead of just Enter:

    =SUM(1/COUNTIF(A1:A800,A1:A800))

    This assumes that there are no blanks in the list.

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: No of instances in a list (Excel 2000 SP3)

    Well that certainly appears to work, Hans. Many thanks, as ever!

    I thought it would be some version of COUNTIF, but I confess I've not heard of array formulas. Could you take a few seconds to explain these, and what the formula is doing (just to save me posting dumb questions on this subject in future).

    Thanks

    Neil

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: No of instances in a list (Excel 2000 SP3)

    See <post#=531,498>post 531,498</post#> and its attachment. If you would like to know more about the amazing possibilities of array formulas, see Array Formulas by Bob Umlas.

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: No of instances in a list (Excel 2000 SP3)

    Blimy, array formulas are obviously very powerful!

    I'll read through Bob's stuff over the next few days...

    Thanks Hans

    Neil

Posting Permissions

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