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

 

[DaKen]

The result I need is 37258=2,37289=1 and 37317=1.
I'm trying to count the number of unique customers ordering each day.

 

[Juan Pablo González]

Ok, sorry, didn't read the entire post, should do that !!

Don't know how this may help the situation. But, if your data is in A2:B1000 add this to column C.

In C1 put Clients
In C2:C1000 put this formula

=1/SUMPRODUCT(($A$2:$A$1000=$A2)*($B$2:$B$1000=$B2))

Now, add or modify the pivot table, this time include as Row the 'Date' and as Data put 'Clients'. Make sure that 'Sum' is the function selected.

 

[Juan Pablo González]

Another option, more efficient but less "pretty" would be to create the pivot table using only columns A and B, like this:

In Row put:
'Dates'
'Acc Nums'

Date should appear first. Double click on it and Uncheck the Subtotals option.

In the Data put:
'Count of Acc Nums'

In that Pivot Table, each ROW represents an account number happening on a specific date.

 

[Aladin Akyurek]

But, Juan, those are wrong freqs!

Aladin

On 2002-02-22 07:49, Juan Pablo G. wrote:
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.

 

[Juan Pablo González]

On 2002-02-22 12:09, Aladin Akyurek wrote:
But, Juan, those are wrong freqs!

Aladin

I know... i didn't read the entire post. Read my latter answers. (We have to get that thread view thing!)

 

[Anne Troy]

LOL. Can't you just use Data-Subtotals? Just sort by date, then by account. The first time you run the subtotals, do a count on the date column. Run the subtotals again, doing a count on the account column, and being sure to uncheck *replace current subtotals*.

 

[Aladin Akyurek]

On 2002-02-21 13:50, DaKen wrote:
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?

DaKen,

Yes, the array-formula that I gave you copied to 10,000 cells or more will be a pain performancewise, as you observed. I have a different proposal, again formula-based. This proposal trades memory against speed. I'm curious about how it performs ( I don't have 10,000 records to check its performance )

I still assume our sample data in A1:C7. That is:

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

And unique dates in D2:D4, that is:

{37258;37289;37317}

In E2 enter:

="B"&MATCH(D2,A:A,0)&":"&"B"&MATCH(IF(D3,D3,D2),A:A,0)-IF(D3,1,0)

Copy down this as far as needed.

In F2 enter:

=SUM(IF(FREQUENCY(MATCH(INDIRECT(E2),INDIRECT(E2),0),MATCH(INDIRECT(E2),INDIRECT(E2),0))>0,1))

Copy down this as far as needed.

Caveat. I assume that the data are sorted on dates.

Note that there are NO formulas here that need to be array-entered.

What you get to see in D:F is:

{37258,"B2:B4",2;
37289,"B5:B6",1;
37317,"B7:B7",1}

Aladin