Count Unique values between two dates - if the value has not appeared at all

[Cambo99]

Hello,

I need to count the unique values of a list between two dates - but only if that particular value has not already appeared. Some context might help.
I have a list of all customer receipts with the data as follows;

Column A: Receipt date
Column B: Customer name
Column C: Receipt amount

I need to know the number of new customer receipts that have been receipted in a particular date range (from cell G7 to cell H7). I can count how many receipts have been made by using the COUNTIFS function, but I have not been able to limit this to new customers only.

My current formula is =COUNTIFS(A:A,">="&G7,A:A,"<="&H7)

I am now stuck with how to add the criteria for unique names / values.

Can someone please help?
Many thanks in anticipation

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

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

 

[Cambo99]

Thank you Aladin for the quick reply. This works great in counting how many unique deposits were received in a particular date range. However is there a way to only return a value if the customer has not already made a deposit?
Probably an example might help the explanation. If someone deposits two lots of money in July and two in August, is it possible to count this only in once in July and not at all in August?

 

[Aladin Akyurek]

Thank you Aladin for the quick reply. This works great in counting how many unique deposits were received in a particular date range. However is there a way to only return a value if the customer has not already made a deposit?
Probably an example might help the explanation. If someone deposits two lots of money in July and two in August, is it possible to count this only in once in July and not at all in August?

The formula I posted does a unique count between two receipt dates.

It seems you want a monthly unique count of names with first time deposits. Try to post a small sample with the expected output though.

 

[Cambo99]

Yes, I really liked the formula that you posted for the unique count between two receipt dates.
A sample of data that I am trying to work with is as follows;

Date Name Amount
12/07/2017 William Cosgrove 81,000.00
14/07/2017 Hugo Bunker 20,000.00
14/07/2017 Mia Bunker 87,000.00
19/07/2017 William Cosgrove 3,000.00
19/07/2017 William Cosgrove 53,000.00
19/07/2017 William Cosgrove 61,000.00
19/07/2017 William Cosgrove 74,000.00
25/07/2017 Charlotte Siede 38,000.00
09/08/2017 William Cosgrove 73,000.00 has already deposited money in July 2017 so needs to be excluded from August 2017 count
10/08/2017 Erin Janssen 2,000.00
15/08/2017 Hugo Bunker 2,000.00 has already deposited money in July 2017 so needs to be excluded from August 2017 count
15/08/2017 Mia Bunker 96,000.00 has already deposited money in July 2017 so needs to be excluded from August 2017 count
30/08/2017 Oliver Wyselaskie 50,000.00
24/08/2017 Stephanie McDermott 85,000.00
05/09/2017 Emily Mirams 27,000.00
19/09/2017 Emily Mirams 54,000.00
27/09/2017 Archie Foy 8,000.00
27/09/2017 Hayley Foy 91,000.00
05/10/2017 Holly Butlin 59,000.00
09/10/2017 Mary Pie 69,000.00
24/10/2017 Erin Janssen 59,000.00
30/10/2017 Skye Mackness 61,000.00

Then the results that I am after would be as follows;
Start Date End Date New Receipts
1/07/2017 31/07/2017 5
1/08/2017 31/08/2017 3
1/09/2017 30/09/2017 3
1/10/2017 31/10/2017 3

I hope that this better explains it - Thank you

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr][tr][td]

1​

[/td][td]Date[/td][td]Name[/td][td]Amount[/td][td]Start Date[/td][td]End date[/td][td]New Receipts[/td][/tr]
[tr][td]

2​

[/td][td]

7/12/2017​

[/td][td]William Cosgrove[/td][td]

81,000.00​

[/td][td]

7/1/2017​

[/td][td]

7/31/2017​

[/td][td]

4​

[/td][/tr]
[tr][td]

3​

[/td][td]

7/14/2017​

[/td][td]Hugo Bunker[/td][td]

20,000.00​

[/td][td]

8/1/2017​

[/td][td]

8/31/2017​

[/td][td]

3​

[/td][/tr]
[tr][td]

4​

[/td][td]

7/14/2017​

[/td][td]Mia Bunker[/td][td]

87,000.00​

[/td][td]

9/1/2017​

[/td][td]

9/30/2017​

[/td][td]

3​

[/td][/tr]
[tr][td]

5​

[/td][td]

7/19/2017​

[/td][td]William Cosgrove[/td][td]

3,000.00​

[/td][td]

10/1/2017​

[/td][td]

10/31/2017​

[/td][td]

3​

[/td][/tr]
[tr][td]

6​

[/td][td]

7/19/2017​

[/td][td]William Cosgrove[/td][td]

53,000.00​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td]

7/19/2017​

[/td][td]William Cosgrove[/td][td]

61,000.00​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td]

7/19/2017​

[/td][td]William Cosgrove[/td][td]

74,000.00​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td]

7/25/2017​

[/td][td]Charlotte Siede[/td][td]

38,000.00​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td]

8/9/2017​

[/td][td]William Cosgrove[/td][td]

73,000.00​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td]

8/10/2017​

[/td][td]Erin Janssen[/td][td]

2,000.00​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

12​

[/td][td]

8/15/2017​

[/td][td]Hugo Bunker[/td][td]

2,000.00​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

13​

[/td][td]

8/15/2017​

[/td][td]Mia Bunker[/td][td]

96,000.00​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

14​

[/td][td]

8/30/2017​

[/td][td]Oliver Wyselaskie[/td][td]

50,000.00​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

15​

[/td][td]

8/24/2017​

[/td][td]Stephanie McDermott[/td][td]

85,000.00​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

16​

[/td][td]

9/5/2017​

[/td][td]Emily Mirams[/td][td]

27,000.00​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

17​

[/td][td]

9/19/2017​

[/td][td]Emily Mirams[/td][td]

54,000.00​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

18​

[/td][td]

9/27/2017​

[/td][td]Archie Foy[/td][td]

8,000.00​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

19​

[/td][td]

9/27/2017​

[/td][td]Hayley Foy[/td][td]

91,000.00​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

20​

[/td][td]

10/5/2017​

[/td][td]Holly Butlin[/td][td]

59,000.00​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

21​

[/td][td]

10/9/2017​

[/td][td]Mary Pie[/td][td]

69,000.00​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

22​

[/td][td]

10/24/2017​

[/td][td]Erin Janssen[/td][td]

59,000.00​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

23​

[/td][td]

10/30/2017​

[/td][td]Skye Mackness[/td][td]

61,000.00​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

A2:A23 >> Date
B2:B23 >> Name
C2:C23 >> Amount

In G2 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF(1-(Name=""),IF(Date-DAY(Date)+1=E2,IF(ISNA(MATCH(Name,IF(Date<E2,Name),0)),MATCH(Name,Name,0)))),ROW(Date)-ROW(INDEX(Date,1,1))+1),1))

Note. The formula does not test the Amount range. The assumption is that Amount is always > 0 if it occurs.

 

[Cambo99]

Thank you - your assumption is correct that the formula does not test the amount range as if it occurs it is always > 0.
But is the formula given incomplete?

 

[Aladin Akyurek]

Thank you - your assumption is correct that the formula does not test the amount range as if it occurs it is always > 0.
But is the formula given incomplete?

?

 

[Cambo99]

The formula is only as follows
=SUM(IF(FREQUENCY(IF(1-(Name=""),IF(Date-DAY(Date)+1=E2,IF(ISNA(MATCH(Name,IF(Date

 

[Aladin Akyurek]

The formula is only as follows
=SUM(IF(FREQUENCY(IF(1-(Name=""),IF(Date-DAY(Date)+1=E2,IF(ISNA(MATCH(Name,IF(Date

I hate html. Period.

The gobbled formula is:

=SUM(IF(FREQUENCY(IF(1-(Name=""),IF(Date-DAY(Date)+1=E2,IF(ISNA(MATCH(Name,IF(Date < E2,Name),0)),MATCH(Name,Name,0)))),ROW(Date)-ROW(INDEX(Date,1,1))+1),1))

You need to confirm the formula with control+shift+enter, not just enter.