Shanan212

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!

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!

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Hi,

Please see attached.

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

Thanks so far!

Sample.xlsx

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.

ASKER

Pivot is an option but user is unfamilier.

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

Lets see

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

Lets see

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Thanks guys!

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

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

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