Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Counting occurences of data

Counting occurences of data

Counting occurences of data

I'm trying to create a function that will count the number of unique data points in a list


I need to develop a calculation that will give me the answer of 4 in the above list. I've tried @rank(@max(range1),range1,0), but it does not handle duplicate occurances of the same number.

Thank you for any help

RE: Counting occurences of data

I think this might work, but I haven't exhaustively tested it:


where your number occupy the range A2..A6.

RE: Counting occurences of data


The suggestion above does NOT work.
If get another (hopefully better) idea, I'll revert.

RE: Counting occurences of data

This is one type of task that Excel can do much better (using an array formula).

Method 1

If I needed to do this task, I would be a /Data Query Unique. In R5, after doing this, the formula @info("dbrecordcount") would hold the value of the number of items extracted, which would be the value you need. Unfortunately, this function was removed from R9 releases.

Method 2

Create an array of formulas:

3            @IF(@ISERR(@MATCH(A2,A1..A$2,0)),1,0)
5            copy this formula down

            the total will be the sum of column B + 1

Method 3

Create a database self-join. The label "List" appears in cell A1 above the first data item. You need a blank column to the right of your list. Place a label, i.e., "Dummy" there. Two ranges get created, a single column range named DATA beginning from cell A1 and and 2 column range named DATA2 which includes cell B1.

The self-join formula:


give the number of unique items.

Method 4

This would be a looping macro.
I won't compose this unless you want that kind of solution.

RE: Counting occurences of data

Thanks, I'm going to give method 2 a shot.  I've done something similar with lotus macro programming.  I appreciate your input.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close