Count of non duplicate account numbers on a specific date

[waaah21]

My file has two tabs, Data and Counts.
I paste data from a different report onto the Data tab to get certain counts of different criterias.
The one I am stuck on is the following:
Need a formula to get a count of non duplicate account numbers on a specific date.

Date	Account
1/12/2015	3414169
1/12/2015	3414169
1/13/2015	9589558
1/13/2015	7510753
1/13/2015	9510901
1/13/2015	9510901
1/14/2015	4281860
1/14/2015	4222873

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

With the sample data above if i needed the count for 1/13/2015 then the answer should be 3.

Thank you for the help.

 

[AhoyNC]

Maybe:

Excel Workbook
	A	B	C	D	E
1	Date	Account
2	1/12/2015	3414169		Date	1/13/2015
3	1/12/2015	3414169		Num. Accts.	3
4	1/13/2015	9589558
5	1/13/2015	7510753
6	1/13/2015	9510901
7	1/13/2015	9510901
8	1/14/2015	4281860
9	1/14/2015	4222873
Sheet1

 

[waaah21]

Maybe:
Sheet1

*	A	B	C	D	E
1	Date	Account	*	*	*
2	1/12/2015	3414169	*	Date	1/13/2015
3	1/12/2015	3414169	*	Num. Accts.	3
4	1/13/2015	9589558	*	*	*
5	1/13/2015	7510753	*	*	*
6	1/13/2015	9510901	*	*	*
7	1/13/2015	9510901	*	*	*
8	1/14/2015	4281860	*	*	*
9	1/14/2015	4222873	*	*	*

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 92px;"><col style="width: 100px;"><col style="width: 64px;"><col style="width: 94px;"><col style="width: 68px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula E3 =SUMPRODUCT(--($A$2:$A$9=$E$2),1/COUNTIF($B$2:$B$9,$B$2:$B$9&"")) <tbody> </tbody>

<tbody>
</tbody>

Tried to use it to encompass the entire column from a different tab because the number of lines change daily but got an error: =SUMPRODUCT(--(PendingMoveOrders!K:K=A1),1/COUNTIF(PendingMoveOrders!W:W,PendingMoveOrders!W:W&"")) any help would be appreciated.

 

[AhoyNC]

Your formula works when I tried it (see below). It's usually not a good idea to reference a whole column. If you know your data will never go beyond say 1000 rows then I would use a formula similar to the one in B3 of the example. If this doesn't work let us know what error you are getting.

Excel Workbook
	A	B	C
1	1/13/2015	3
2		3
3
Sheet1

Excel Workbook
	J	K	L	V	W	X
1		Date			Account
2		1/12/2015			3414169
3		1/12/2015			3414169
4		1/13/2015			9589558
5		1/13/2015			7510753
6		1/13/2015			9510901
7		1/13/2015			9510901
8		1/14/2015			4281860
9		1/14/2015			4222873
10
11
PendingMoveOrders

 

[waaah21]

I am getting #VALUE!
*A Value used in the formula is of the wrong data type.*

 

[GerryZ]

Try This!
CTRL+SHIFT+ENTER and not just ENTER

<table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:14pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="width:30px; " /><col style="width:118.4px;" /><col style="width:93.6px;" /><col style="width:24.8px;" /><col style="width:120px;" /><col style="width:165.6px;" /></colgroup><tr style="background-color:#cacaca; text-align:center;font-size:8pt; "><td > </td><td style=" border-width:3px; border-style:solid;border-color:#f0f0f0; ">A</td><td style=" border-width:3px; border-style:solid;border-color:#f0f0f0; ">B</td><td style=" border-width:3px; border-style:solid;border-color:#f0f0f0; ">C</td><td style=" border-width:3px; border-style:solid;border-color:#f0f0f0; ">D</td><td style=" border-width:3px; border-style:solid;border-color:#f0f0f0; ">E</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; border-style:solid; border-width:1px; border-color:#000000; ">Date</td><td style="background-color:#ccffcc; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Account</td><td style=" border-right-style:solid; border-right-width:1px; border-right-color:#000000; "> </td><td style="background-color:#ff0000; color:#ffffff; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">unique data</td><td style="background-color:#0066cc; color:#ffffff; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">unique accont x data</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">01/12/2015</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">3414169</td><td style=" border-right-style:solid; border-right-width:1px; border-right-color:#000000; "> </td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">12/01/2015</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">1</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">01/12/2015</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">3414169</td><td style=" border-right-style:solid; border-right-width:1px; border-right-color:#000000; "> </td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">13/01/2015</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">3</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">01/13/2015</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">9589558</td><td style=" border-right-style:solid; border-right-width:1px; border-right-color:#000000; "> </td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">14/01/2015</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">2</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">01/13/2015</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">7510753</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">01/13/2015</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">9510901</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">01/13/2015</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">9510901</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">01/14/2015</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">4281860</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">01/14/2015</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">4222873</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >{=IFERROR(INDEX<span style=' color:008000; '>(dates,SMALL<span style=' color:#0000ff; '>(IF<span style=' color:#ff0000; '>(FREQUENCY<span style=' color:#804000; '>(dates,dates)</span>,ROW<span style=' color:#804000; '>(dates)</span>-ROW<span style=' color:#804000; '>($A$2)</span>+1)</span>,ROWS<span style=' color:#ff0000; '>($D$2:D2)</span>)</span>)</span>,"")}</td></tr><tr><td >E2</td><td >{=SUMPRODUCT(--<span style=' color:008000; '>($A$2:$A$10=D2)</span>,--<span style=' color:008000; '>(FREQUENCY<span style=' color:#0000ff; '>(accounts,accounts)</span>>0)</span>)}</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[AhoyNC]

Are the dates in column W actual dates or are they text, also are account #'s numeric or text?
Where is the data coming from since you said it changes daily.
I have not been able to duplicate the issue, when I change the data type the formula returns a 0, but not an error. Usually a #VALUE would be something like dividing a number by a text.

 

[GerryZ]

My file has two tabs, Data and Counts.
I paste data from a different report onto the Data tab to get certain counts of different criterias.
The one I am stuck on is the following:
Need a formula to get a count of non duplicate account numbers on a specific date.

Date	Account
1/12/2015	3414169
1/12/2015	3414169
1/13/2015	9589558
1/13/2015	7510753
1/13/2015	9510901
1/13/2015	9510901
1/14/2015	4281860
1/14/2015	4222873

<TBODY>
</TBODY>

With the sample data above if i needed the count for 1/13/2015 then the answer should be 3.

Thank you for the help.

Oppsss! do not consider my first solution
whatch this!
<table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:14pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="width:30px; " /><col style="width:118.4px;" /><col style="width:116px;" /><col style="width:24.8px;" /><col style="width:120px;" /><col style="width:254.4px;" /></colgroup><tr style="background-color:#cacaca; text-align:center;font-size:8pt; "><td > </td><td style=" border-width:3px; border-style:solid;border-color:#f0f0f0; ">A</td><td style=" border-width:3px; border-style:solid;border-color:#f0f0f0; ">B</td><td style=" border-width:3px; border-style:solid;border-color:#f0f0f0; ">C</td><td style=" border-width:3px; border-style:solid;border-color:#f0f0f0; ">D</td><td style=" border-width:3px; border-style:solid;border-color:#f0f0f0; ">E</td></tr><tr style="height:75px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; border-style:solid; border-width:1px; border-color:#000000; ">Date</td><td style="background-color:#ccffcc; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Account</td><td style=" border-right-style:solid; border-right-width:1px; border-right-color:#000000; "> </td><td style="background-color:#ff0000; color:#ffffff; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">unique data</td><td style="background-color:#0066cc; color:#ffffff; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">UNIQUE COUNT NO DUPS DATE</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">01/12/2015</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">3414169</td><td style=" border-right-style:solid; border-right-width:1px; border-right-color:#000000; "> </td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">12/01/2015</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">1</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">01/12/2015</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">3414169</td><td style=" border-right-style:solid; border-right-width:1px; border-right-color:#000000; "> </td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">13/01/2015</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">3</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">01/13/2015</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">9589558</td><td style=" border-right-style:solid; border-right-width:1px; border-right-color:#000000; "> </td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">14/01/2015</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">2</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">01/13/2015</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">7510753</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">01/13/2015</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">9510901</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">01/13/2015</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">9510901</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">01/14/2015</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">4281860</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">01/14/2015</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">4222873</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >{=IFERROR(INDEX<span style=' color:008000; '>(dates,SMALL<span style=' color:#0000ff; '>(IF<span style=' color:#ff0000; '>(FREQUENCY<span style=' color:#804000; '>(dates,dates)</span>,ROW<span style=' color:#804000; '>(dates)</span>-ROW<span style=' color:#804000; '>($A$2)</span>+1)</span>,ROWS<span style=' color:#ff0000; '>($D$2:D2)</span>)</span>)</span>,"")}</td></tr><tr><td >E2</td><td >{=SUM(IF<span style=' color:008000; '>(FREQUENCY<span style=' color:#0000ff; '>(IF<span style=' color:#ff0000; '>(accounts<>"",IF<span style=' color:#804000; '>(dates=D2,accounts)</span>)</span>,accounts)</span>>0,1)</span>)}</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[Aladin Akyurek]

My file has two tabs, Data and Counts.
I paste data from a different report onto the Data tab to get certain counts of different criterias.
The one I am stuck on is the following:
Need a formula to get a count of non duplicate account numbers on a specific date.

Date	Account
1/12/2015	3414169
1/12/2015	3414169
1/13/2015	9589558
1/13/2015	7510753
1/13/2015	9510901
1/13/2015	9510901
1/14/2015	4281860
1/14/2015	4222873

<tbody>
</tbody>

With the sample data above if i needed the count for 1/13/2015 then the answer should be 3.

Thank you for the help.

Control+shift+enter, not just enter...

=SUM(IF(FREQUENCY(IF(account<>"",IF(date=$E2,MATCH(account,account,0))),ROW(account)-ROW(INDEX(account,1,1))+1),1))

where E2 houses a date of interest like 1/13/2015.

 

[GerryZ]

Oppppss!!

as you can see I put the dates with different costum number formmatting
Column A is USA custom number formatting and in Column D is european custom Number but both are the same Excel serial number