count occurences

DaKen

Board Regular
Joined
Feb 18, 2002
Messages
55
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!!
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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
 
Upvote 0
It worked with one problem. The answer on 37317 was supposed to be 1, but it returned 0.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top