Thanks:  0
Likes:  0

1. I need to count the number of unique occurrences of 2 feilds being the same in a spreadsheet. I have a list of invoices including dates in one coulumn and account names in another. Some accounts have multiple invoices on the same day. I want to be able to count how many unique customers per day.

Thanks for the help!!

2. Lets say that A1:B7 houses the following sample:

{"Dates","Acc Nums";
37258,"acc1";
37258,"acc2";
37258,"acc1";
37289,"acc3";
37289,"acc3";
37317,"acc1"}

Funny looking numbers are actually dates.

Create a unique list of dates of interest in D from D2 on like the one that follows:

{37258;
37289;
37317}

which is derived from the previous sample of data.

In E2 array-enter:

=SUM(N(FREQUENCY(IF((\$A\$2:\$A\$7=D2),MATCH(\$B\$2:\$B\$7,\$B\$2:\$B\$7,0)),MATCH(\$B\$2:\$B\$7,\$B\$2:\$B\$7,0))>0))

In order to array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

Copy the above formula down as far as needed.

You'll get the following to see in D and E:

{37258,2;
37289,1;
37317,1}

which gives you the number of different accounts that are invoiced on a given date.

3. It worked with one problem. The answer on 37317 was supposed to be 1, but it returned 0.

4. On 2002-02-21 09:20, DaKen wrote:
It worked with one problem. The answer on 37317 was supposed to be 1, but it returned 0.
DaKen: Go thru instructions & try again. If you still can't get it right, I can send you the example WB.

5. I still get the same result. I think it may be in how I entered the unigue dates in column d. I entered each unique date in a seperate cell, d2,d3,d4. Maybe you could send the sheet?

6. On 2002-02-21 11:05, DaKen wrote:
I still get the same result. I think it may be in how I entered the unigue dates in column d. I entered each unique date in a seperate cell, d2,d3,d4. Maybe you could send the sheet?
akyurek@xs4all.nl

or provide yours.

7. This process is working, but it crashes or nearly crashes the program. I have almost 10,000 records in the array. Is there a different way to get the info?

8. Use a Pivot Table, put your 'DateField' both in as a Row and as Data. Make sure you have 'Count of' selected.

9. The pivot table is great, but I haven't worked with them much and it returns the total # of records for each date. I need just the number of unique customer records for each date.

Any help?
Thanks

10. Using Aladin's test data, I produced this Pivot Table.

{"Cout of Dates",0;
"Dates","Total";
37258,3;
37289,2;
37317,1;
"Total General",6}

Where the "funny lucking numbers" are dates.

## User Tag List

#### Posting Permissions

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