Hi,
I have read through most pages in this thread bu couldn't find a solution to my (quite complex) problem.
The data represents customers and their opened accounts. What I want to achieve is a monthly count on the number of new and lost customers, while also making sure that returning customers (i.e. customers that once opened an account, then closed all their accounts, then came back later) are counted.
1. Blank cells will be included in the data material and should not be counted
2. Only the first instance of a customer in column A should be counted, UNLESS a date exists in column C for all instances of that same customer (e.g. see row 3 & 4 and compare to row 8)
Data material
<tbody>
</tbody>Expected results
<tbody>
</tbody>
I came very close with this formula, but alas it's missing the component which looks for if the customer has closed all his accounts in the past and then came back as a customer, and I just can't figure out how to get it in there.
I have read through most pages in this thread bu couldn't find a solution to my (quite complex) problem.
The data represents customers and their opened accounts. What I want to achieve is a monthly count on the number of new and lost customers, while also making sure that returning customers (i.e. customers that once opened an account, then closed all their accounts, then came back later) are counted.
1. Blank cells will be included in the data material and should not be counted
2. Only the first instance of a customer in column A should be counted, UNLESS a date exists in column C for all instances of that same customer (e.g. see row 3 & 4 and compare to row 8)
Data material
| Name | Start date | Close date |
| AA | 2016-09-05 | |
| BB | 2013-10-25 | 2016-01-27 |
| BB | 2015-09-28 | 2016-01-27 |
| BB | 2016-08-01 | |
| CC | 2016-06-22 | |
| CC | 2016-07-01 | |
| DD | 2016-08-31 | |
| DD | 2016-09-21 |
<tbody>
</tbody>
| Month | Acquired customer | Lost customer |
| 2013-10-01 | 1 | 0 |
| 2015-09-01 | 0 | 0 |
| 2016-01-01 | 0 | 1 |
| 2016-06-01 | 1 | 0 |
| 2016-07-01 | 0 | 0 |
| 2016-08-01 | 2 | 0 |
| 2016-09-01 | 1 | 0 |
<tbody>
</tbody>
I came very close with this formula, but alas it's missing the component which looks for if the customer has closed all his accounts in the past and then came back as a customer, and I just can't figure out how to get it in there.
Code:
=COUNT(IF(FREQUENCY(IF($A$2:$A$10<>"";MATCH($A$2:$A$10;$A$2:$A$10;0));ROW($A$2:$A$10)-ROW($A$2)+1);IF($B$2:$B$10>EOMONTH(F26;-1);IF($B$2:$B$10<=EOMONTH(F26;0);1))))
