Getting distinct counts from multiple columns

[Ody]

Hello,

I'm trying to figure out how to calculate a count of unique records across two columns (customer number and acct number) with out much luck.
For example, a customer number may appear 3 times but be associated with 3 separate acct numbers.

I've found a lot of in-depth formulas online but I'm not sure if they're what I need. I think I need some sort of array formula but that's been a weakness of mine so I'm a little stuck.

Appreciate any advice, thanks!

 

[Ody]

Thanks for the reply T Valko,

My cust number is a 30 digit number (stored as text) and looks something like 002600000000000000000002432162.

My acct number is a 23-30 digit number (stored as text) and looks something like 002600000000000000000000618.

I'm trying to determine how many customers there are per account number. In most cases it's one per account but sometimes there are multiple customer numbers associated with the same account number.

I hope this is sufficient info...

 

[T. Valko]

Thanks for the reply T Valko,

My cust number is a 30 digit number (stored as text) and looks something like 002600000000000000000002432162.

My acct number is a 23-30 digit number (stored as text) and looks something like 002600000000000000000000618.

I'm trying to determine how many customers there are per account number. In most cases it's one per account but sometimes there are multiple customer numbers associated with the same account number.

I hope this is sufficient info...

Try this array formula**:

=SUM(IF(FREQUENCY(IF(B2:B20=D2,MATCH(A2:A20,A2:A20,0)),ROW(A2:A20)-ROW(A2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Column A = customer number
Column B = account number
D2 = some account number

Assumes no empty cells within the data range of column A.

 

[Ody]

Again, thanks T Valko.

I have no empty rows in my customer column and I can adjust the range to accommodate the size of my table.

I have one question though. Why is D2 "some account number"? What is that supposed to represent?

 

[T. Valko]

Try this array formula**:

=SUM(IF(FREQUENCY(IF(B2:B20=D2,MATCH(A2:A20,A2:A20,0)),ROW(A2:A20)-ROW(A2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Column A = customer number
Column B = account number
D2 = some account number

Assumes no empty cells within the data range of column A.

Again, thanks T Valko.

I have no empty rows in my customer column and I can adjust the range to accommodate the size of my table.

I have one question though. Why is D2 "some account number"? What is that supposed to represent?

The formula is counting the unique customer numbers in column A for the account number entered in D2.

Or, maybe you want it the other way around: count the unique account numbers for a particular customer number?

 

[Ody]

Hi Valko,

I'm more interested in finding out how many customers there are per account number, keeping in mind that a customer number can be repeated in my array more than once if it was used to open more than one account.

The source of my confusion with your original reply is that i only have 2 columns of numbers, customer and account. Am I creating a copy of the account numbers in column D?

 

[T. Valko]

Hi Valko,

I'm more interested in finding out how many customers there are per account number, keeping in mind that a customer number can be repeated in my array more than once if it was used to open more than one account.

The source of my confusion with your original reply is that i only have 2 columns of numbers, customer and account. Am I creating a copy of the account numbers in column D?

Can you post some sample data and tell us what result(s) you expect?

You don't need to use sample data that has those (ridiculously) long account/customer numbers. Use some made up data that is easy to read/type.

 

[Ody]

Sorry, let me try this again...

<table width="190" border="0" cellpadding="0" cellspacing="0"><col style="mso-width-source:userset;mso-width-alt:3986;width:82pt" width="109"> <col style="mso-width-source:userset;mso-width-alt:2962;width:61pt" width="81"> <tbody><tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;width:82pt" width="109" height="17">cust num</td> <td class="xl24" style="width:61pt" width="81">acct num</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">123</td> <td class="xl24">7777</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">123</td> <td class="xl24">8888</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">123</td> <td class="xl24">9999</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">456</td> <td class="xl24">7777</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">456</td> <td class="xl24">1010</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">879</td> <td class="xl24">1012</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">789</td> <td class="xl24">7777</td> </tr> </tbody></table>

So in the above example I want to know that acct num 777 has 3 cust associated with it.

I want unique acct nums and a count of unique customers associated with that acct num.

Does this make a little more sense?

 

[Aladin Akyurek]

Sorry, let me try this again...

<TABLE border=0 cellSpacing=0 cellPadding=0 width=190><COLGROUP><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="WIDTH: 82pt; HEIGHT: 12.75pt" class=xl24 height=17 width=109>cust num</TD><TD style="WIDTH: 61pt" class=xl24 width=81>acct num</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" class=xl24 height=17>123</TD><TD class=xl24>7777</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" class=xl24 height=17>123</TD><TD class=xl24>8888</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" class=xl24 height=17>123</TD><TD class=xl24>9999</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" class=xl24 height=17>456</TD><TD class=xl24>7777</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" class=xl24 height=17>456</TD><TD class=xl24>1010</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" class=xl24 height=17>879</TD><TD class=xl24>1012</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" class=xl24 height=17>789</TD><TD class=xl24>7777</TD></TR></TBODY></TABLE>

So in the above example I want to know that acct num 777 has 3 cust associated with it.

I want unique acct nums and a count of unique customers associated with that acct num.

Does this make a little more sense?

Let A1:A8 house cust nums and B1:B8 acct num's, the headers included.

Let also E2 house 7777, an acct num of interest.

F2, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$2:$A$8<>"",IF($B$2:$B$8=E2,
  MATCH($A$2:$A$8,$A$2:$A$8,0))), ROW($A$2:$A$8)-ROW($A$2)+1),1))

 

[T. Valko]

Sorry, let me try this again...

<TABLE cellSpacing=0 cellPadding=0 width=190 border=0><COLGROUP><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="WIDTH: 82pt; HEIGHT: 12.75pt" width=109 height=17>cust num</TD><TD class=xl24 style="WIDTH: 61pt" width=81>acct num</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="HEIGHT: 12.75pt" height=17>123</TD><TD class=xl24>7777</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="HEIGHT: 12.75pt" height=17>123</TD><TD class=xl24>8888</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="HEIGHT: 12.75pt" height=17>123</TD><TD class=xl24>9999</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="HEIGHT: 12.75pt" height=17>456</TD><TD class=xl24>7777</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="HEIGHT: 12.75pt" height=17>456</TD><TD class=xl24>1010</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="HEIGHT: 12.75pt" height=17>879</TD><TD class=xl24>1012</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="HEIGHT: 12.75pt" height=17>789</TD><TD class=xl24>7777</TD></TR></TBODY></TABLE>

So in the above example I want to know that acct num 777 has 3 cust associated with it.

I want unique acct nums and a count of unique customers associated with that acct num.

Does this make a little more sense?

OK, the formula I posted in reply #12 does what you want.

Here it is again:

Book1
	A	B	C	D	E
1	Customer	Account	_	Account	Count
2	123	7777	_	7777	3
3	123	8888	_	_	_
4	123	9999	_	_	_
5	456	7777	_	_	_
6	456	1010	_	_	_
7	879	1012	_	_	_
8	789	7777	_	_	_
Sheet1

This array formula** entered in E2:

=SUM(IF(FREQUENCY(IF(B2:B8=D2,MATCH(A2:A8,A2:A8,0)),ROW(A2:A8)-ROW(A2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.