count occurences

[DaKen]

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!!

[Aladin Akyurek]

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.

Aladin

[DaKen]

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

[Aladin Akyurek]

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.

[DaKen]

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?

[Aladin Akyurek]

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.

Aladin

[DaKen]

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?

[Juan Pablo González]

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

[DaKen]

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

[Juan Pablo González]

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.