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.

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Date[/TD]
[TD="class: xl63, width: 64"]Account[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]1/12/2015[/TD]
[TD="align: right"]3414169[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]1/12/2015[/TD]
[TD="align: right"]3414169[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]1/13/2015[/TD]
[TD="align: right"]9589558[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]1/13/2015[/TD]
[TD="align: right"]7510753[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]1/13/2015[/TD]
[TD="align: right"]9510901[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]1/13/2015[/TD]
[TD="align: right"]9510901[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]1/14/2015[/TD]
[TD="align: right"]4281860[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]1/14/2015[/TD]
[TD="align: right"]4222873[/TD]
[/TR]
</tbody>[/TABLE]


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.

 

[GerryZ]

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.

hello Aladin!
You are my teacher for ever.... but now i don't understand why you did longer formula with the MATCH.... and ROW....as i Know the FREQUENCY function when Match number does not need to convert number in number( because they are already numbers).... if the "account" Range was texts would be good to convert in that way but in this case for the fact you have numbers you didn't need (MATCH....(ROW... inside the FREQUENCY see my formula #8
it gives you back the same outcome but shorter formula!
Thank you!

 

[Aladin Akyurek]

hello Aladin!
You are my teacher for ever.... but now i don't understand why you did longer formula with the MATCH.... and ROW....as i Know the FREQUENCY function when Match number does not need to convert number in number( because they are already numbers).... if the "account" Range was texts would be good to convert in that way but in this case for the fact you have numbers you didn't need (MATCH....(ROW... inside the FREQUENCY see my formula #8
it gives you back the same outcome but shorter formula!
Thank you!

If accounts are text numbers, a generic approach is needed. Otherwise, applying FREQUENCY function directly on numbers is the right thing to do.

 

[waaah21]

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.

Yes there was TEXT in the column.... I feel so frustrated sometimes when the issue is such a simple one and i can't get my head around it. Thanks you GUYS!

 

[waaah21]

Thank you AhoyNC it worked out great!!!

 

[waaah21]

Hi AhoyNYC,

Along the lines of this solution if there was a third condition for example. how would I go about adding to the formula?

If receive date is 30 days or older than Todays date on F1,
AND the SCHEDULED date is equivalent to 01/01/00,
How many non-duplicate account numbers are there?

In this example below it should be 2 unique account numbers.

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD]Receive Date[/TD]
[TD]Scheduled Date[/TD]
[TD]Account number[/TD]
[TD][/TD]
[TD]Todays Date[/TD]
[TD]01/21/15[/TD]
[/TR]
[TR]
[TD]12/01/14
[/TD]
[TD]01/01/00
[/TD]
[TD]789
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/01/14[/TD]
[TD]01/01/00[/TD]
[TD]789[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/01/14[/TD]
[TD]01/01/00[/TD]
[TD]905[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/01/14[/TD]
[TD]1/26/15
[/TD]
[TD]6969[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/15/14
[/TD]
[TD]1/21/15[/TD]
[TD]1234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/20/15[/TD]
[TD]01/01/00[/TD]
[TD]8484[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[AhoyNC]

Here are a few different options.
F3 if you want to hard code the schedule date in the formula.
F6 if you want to enter the schedule date in a separate column.
F9 is an adjustment to Aladin's formula much is an array formula that must be entered with

Excel Workbook
	A	B	C	D	E	F	G
1	Receive Date	Scheduled Date	Account number		Todays Date	1/21/2015
2	12/1/2014	1/1/2000	789
3	12/1/2014	1/1/2000	789			2
4	12/1/2014	1/1/2000	905
5	12/1/2014	1/26/2015	6969		Sch. Date	1/1/2000
6	12/15/2014	1/21/2015	1234			2
7	1/20/2015	1/1/2000	8484
8						Aladin's	Formula
9						2
10
CTRL-SHIFT-ENTER.

 

[Aladin Akyurek]

@AhoyNC
FREQUENCY formula is faster then the SUMPRODUCT version. By the way, the latter is not complete: You also need a term like: --($C$2:$C$7<>"").

 

[AhoyNC]

Aladin,
Thanks for catching that.

Waaah21,
As Aladin stated the FREQUENCY formula will be faster than the formulas I gave you. Also, in case there are in blanks in your data the SUMPRODUCT formulas need to be changed to:

=SUMPRODUCT(--($A$2:$A$7<=$F$1-30),--($B$2:$B$7=DATEVALUE("1/1/2000")),1/COUNTIF($C$2:$C$7,C2:C7&""),[COLOR=#ff0000]--($C$2:$C$7<>""[/COLOR]))
or
=SUMPRODUCT(--($A$2:$A$7<=$F$1-30),--($B$2:$B$7=$F$5),1/COUNTIF($C$2:$C$7,$C$2:$C$7&""),[COLOR=#ff0000]--($C$2:$C$7<>"")[/COLOR])