• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

No of Active Customers per Agent

Yogendra Bharti

New Member
Hi,

I have transaction/date wise data of few no of agents who are involved in Depositing & Withdrawing the money of customers. Now I want to calculate the Active no. of customers per agent, per month.

Here Active customer is defined as "The Customer who has done a minimun one transaction (dep/withdrawal) atleast once in last three months.

The Data is needed Agent ID wise on monthly basis.

Attached is the Data file along with the expected result.

Pls help.

Thanks in Advance.
 

Attachments

  • Active Customer Per Agent.xlsx
    100.2 KB · Views: 7
Hi Yogendra - Made 1 change in Column G of "Data" tab..

Use the following formula in G2 and drag it down : DATEVALUE(F2)

Then for "Expected Result" tab ..

Use the below formula in B3..and copy then drag it to all the cells in range B3 to H15..

COUNTIFS(Data!$A$2:$A$1492,$A3,Data!$G$2:$G$1492,">"&EOMONTH(B$1,-3),Data!$G$2:$G$1492,"<="&B$1)

Unable to attach a file...
 
Hope you dont mind but as im learning excel i look at files and learn from them ... anyway being going through this and its not adding up ...
changed a bit of Asheesh formula as i noticed the 3month totals seemed to be wrong and i did a sheet by month totals ..
and found that the 3 month totals are out ...
i also filtered the data by month on the data sheeth to check my results just to be sure ..
so if you total my month totals for every 3 months you will see differences ...
so might be worth looking at
for example in Asheesh total for august it has 6 for 11040094 which would be totals for Aug +July+June
where as i have 25 and 4 and 2 ... which gives a total of 31 for those 3 months
so it looks like its not adding august
next lad total has 69 and i have 136 and 52 and 7 total 195 ... looks like total of june july and 10 of August so bit strange that anyway attached screen shot and uploaded my file done by month so it can be looked at.
 

Attachments

  • Customer fix Part 3.jpg
    Customer fix Part 3.jpg
    299.6 KB · Views: 4
  • Active Customer Per AgentJohnLOng.xlsx
    108.6 KB · Views: 5
ok after having another look at this i now understand what is going on .... in the top row where the dates are jun-14 .... jul-14 etc .... if you click on these cells you will see in the formula bar they read 1/6/2014 so they are not reading the full month .... change them to end date in formula bar eg 30/6/14 for jun-14 and all the way across to dec which should be 31/12/14 ....
this then sorts out the problem.
i have checked all the numbers by manually filtering on the data sheet to make sure and they match the results i got when working things out

added a image so you can see the results you should expect to see if its done right
 

Attachments

  • Customer fix Part 3A.jpg
    Customer fix Part 3A.jpg
    199.4 KB · Views: 4
Last edited:
Hi @John()

There are 2 things that arise out of the question raised by Yogendra..

1) Assumption no 1- Yogendra is looking for the number of active customers per agent who have either done withdrawal or a deposit transaction in last 3 months..e.g.
For agent ID: 11040094 & month Jun'14 - last 3 months would be Mar'14, Apr'14, May'14..and there are no transactions done..then the first file works fine..
2) Assumption no 2 - Now for the same agent ID and Jun'14 month..If by last 3 months Yogendra means Apr'14, May'14 and Jun'14(inclusive uptil last day of the month), then the logic in post no 6 is perfect..
Here is the formula to second your logic -

COUNTIFS(Data!$A$2:$A$1492,$A3,Data!$G$2:$G$1492,">"&EOMONTH(B$1,-3),Data!$G$2:$G$1492,"<="&EOMONTH(B$1,0))

Also noticed that you have hardcoded your formula post no 5...you can use EOMONTH function to get to the last day of the month..

Neverthless..It was a nice catch.. :)
 
nice Asheesh .... had not considered looking at it that way ... and it makes sense.
also for the tip on EOMONTH ... will save me a bit of time when working with dates
 
Back
Top