Hello,

I have the following table.
C D E
DATE CUSTOMER SERVICE
3-MAY-15 BILL PAID
5-JUN-15 MARRY BASIC
6-FEB-15 MARRY AMC
10-OCT-15 JOI AMC
14-DEC-15 BILL BASIC
4-APR-15 JOI PAID
26-APR-15 JOHN AMC
3-JAN-15 JOHN PAID
3-MAY-15 BILL BASIC

I have random dates in column C.
I have about 50 unique customers in column D
I have 3 fixed types of services in column E (Basic, Paid and AMC)

Assuming NOW() is 20-Apr, i want COUNT of customers who fall into basic, paid and AMC -->at the next nearest future date. Past dates are useless.

Output required is

Total customers = 4
Basic = 2 (Marry and Bill, 5-jun and 3-may respectively)
Paid = 1 (Bill, Not Joi or John as dates < than 20-Apr)
AMC = 1 (John on 26-Apr, Not Marry because date<20-Apr, Not Joi as Joi is in Paid because of 4-Apr<10-Oct

So Sum basic+paid+amc = Total customers. Each customer must fall under 1 unique service category.
**I will never have same date associated with 2 services for 1 customer. Customer does not do 2 services in a single day..

For total customers i did =SUMPRODUCT(1/COUNTIF(D2:D12,D2:D12)) in G1
For customer and service i did =SUM(IF(FREQUENCY(IF(E$2:E$12=G2,MATCH(D$2:D$12,D$2:D$12,0)),ROW(D$2:D$12)-ROW(D$2)+1),1)) in G2 Basic G3 paid and G4 AMC.

This works without dates, but if i try to change any customer from paid to basic, basic+paid+amc is no longer equal to total.

Any help will be greatly appreciated. Thank you!

• Moved by Lanlan HuangMicrosoft contingent staff 6 hours 3 minutes ago

Hi

This is the forum to discuss questions about Microsoft Excel develop(VBA, customization). For your question is about Excel formula, I am moving this thread to the TechNet forum for Excel

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

Thank you for your understanding.

Best Regards