# CountIf with multiple criteria and unique values

#### mackan56

##### New Member
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
 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>
Expected results
 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))))``

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Replies
11
Views
236
Replies
9
Views
147
Replies
5
Views
139
Replies
9
Views
136
Replies
2
Views
148

1,196,235
Messages
6,014,146
Members
441,807
Latest member
sjkenjalo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back