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

1. ## 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).

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. ## 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. ## 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. ## 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. ## 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
•