CountIf with multiple criteria and unique values

mackan56

New Member
Joined
Nov 30, 2016
Messages
5
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
NameStart dateClose date
AA2016-09-05
BB2013-10-252016-01-27
BB2015-09-282016-01-27
BB2016-08-01
CC2016-06-22
CC2016-07-01
DD2016-08-31
DD2016-09-21

<tbody>
</tbody>
Expected results
MonthAcquired customerLost customer
2013-10-0110
2015-09-0100
2016-01-0101
2016-06-0110
2016-07-0100
2016-08-0120
2016-09-0110

<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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top