Link to home
Start Free TrialLog in
Avatar of Shanan212
Shanan212Flag for Canada

asked on

Count unique accounts active within a set time period

Hi,

I have a summary sheet in which I am trying to count the 'accounts' that have activities/values within a month's time

This is how the data looks

Month           Account No       Sales Rep
2012-01         112921              Mary P
2012-01         112923              John S
2012-03         112923              Mary P
2012-03         112922              Mike A
2012-01         112921              Mary P
2012-03         112922              John S
2012-01         112922              Mike A

I want to sort the data in this way:


Month (2012-01)
Rep                    Account Active        
Mary P               1                                  (Mary had 2 accounts of 112921 - hence unique being 1)
John S                2
Mike A               1                                     (Mike had 2 accounts of 112922 - hence unique being 1)


Anyone knows the formula to get this unique count within a time period?

It is something like a countif formula with a unique-finder

Thanks!
Avatar of felixdsouza
felixdsouza
Flag of India image

Seems to me that this kind of counting of unique values is not possible using a formula.  It can be done using a macro, however, resulting in output like you are requesting...

Also, seems to me that for month 2012-01,  John S has only one active account as per your sample data, not 2 as per your expected output...
ASKER CERTIFIED SOLUTION
Avatar of NBVC
NBVC
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Shanan212

ASKER

Hi,

Please see attached.

The 'criteria' section works but the formula is counting duplicate values as well.

Thanks so far!
Sample.xlsx
Counting of unique values is certainly the catch.... otherwise this could easily be achieved with a simple pivot table.
Pivot is an option but user is unfamilier.

As long as that 'counting unique' value section works, it should be charm!

Lets see
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks guys!

Hope my point break-down is ok based on correct answer and attempted to help-out.