Count if all entries of instance has a value in adjacent column

[mackan56]

Hi,

I'm not even sure how to phrase this problem, but I'm hoping some of you clever people can help me out with this.

I have a dataset which contains customer names and dates of opening and closing accounts. I would like to be able to see the number of new customers and customers who have closed down all their accounts on a month-by-month basis.

I was able to create the formula for getting new customers by following another thread in this forum. However, the part about counting the number of customers who have closed all of their accounts, as well as tracking that to a specific month, is a task too great for myself.

Below is what I want to achieve. Keep in mind that only customer AA should be counted as "Closed" since all accounts have been closed, whereas customer CC still has one account remaining.

Thanks in advance!

Data set

Name	Opened	Closed
AA	2016-01-01	2016-04-01
AA	2016-02-01	2016-04-01
AA	2016-03-01	2016-04-01
BB	2016-04-01
BB	2016-05-01
BB	2016-06-01
CC	2016-07-01
CC	2016-08-01	2016-10-01
CC	2016-09-01	2016-10-01

<colgroup><col width="158" span="3" style="width:119pt"> </colgroup><tbody>
</tbody>

Expected results

Month	Opened	Closed
2016-01-01	1	0
2016-02-01	0	0
2016-03-01	0	0
2016-04-01	1	1
2016-05-01	0	0
2016-06-01	0	0
2016-07-01	1	0
2016-08-01	0	0
2016-09-01	0	0
2016-10-01	0	0

<colgroup><col width="158" span="3" style="width:119pt"> </colgroup><tbody>
</tbody>

 

[CyrusTheVirus]

Based on what I understand, shouldn't your data set and expected results have the below changes in red?

Data set

Name	Opened	Closed
AA	2016-01-01	2016-04-01
AA	2016-02-01	2016-04-01
AA	2016-03-01	2016-04-01
BB	2016-04-01
BB	2016-05-01
BB	2016-06-01
CC	2016-07-01	2016-10-01
CC	2016-08-01	2016-10-01
CC	2016-09-01	2016-10-01

<tbody>
</tbody>

Expected results

Month	Opened	Closed
2016-01-01	1
2016-02-01
2016-03-01
2016-04-01	1	1
2016-05-01
2016-06-01
2016-07-01	1
2016-08-01
2016-09-01
2016-10-01		1

<tbody>
</tbody>

 

[mackan56]

Based on what I understand, shouldn't your data set and expected results have the below changes in red?

Hi,

No, as I wrote in the description, customer CC has closed 2 of his accounts and thus still counts as a current customer whereas customer AA has closed all accounts and thus should no longer be counted as a current custmer.

 

[mackan56]

Bumping this, would be great with some help

 

[mackan56]

I feel like I'm very close with this formula, but it still counts the closed column even if just one value has been populated, meaning that one customer can close one account and still be counted as a customer who we've lost.

=SUM(IF(FREQUENCY(MATCH($A$3:$A$11;$A$3:$A$11;0);MATCH($A$3:$A$11;$A$3:$A$11;0))>1;IF(OFFSET($A$3:$A$11;0;2)>0;IF($C$3:$C$11>EOMONTH($I6;-1);IF($C$3:$C$11<=EOMONTH($I6;0);1)))))