How to count Unique Client ID by month and year

H

hetal247

New Member
Joined
Dec 21, 2011
Messages
18
Hi,

I have a table that contains visit data for a number of clients. I want to be able to count the number of unique clients by month and year. i have searched online and have not yet been able to find a solution. an example of my data is below:

Client IDDateCall TypeArea
1​
01/08/2022​
ContactEast
1​
04/08/2022​
ContactEast
1​
09/08/2022​
ContactEast
1​
15/08/2022​
Non-ContactEast
1​
02/09/2022​
ContactEast
1​
06/09/2022​
ContactEast
1​
10/09/2022​
Non-ContactEast
1​
19/09/2022​
Non-ContactEast
1​
26/09/2022​
ContactEast
2​
02/08/2022​
Non-ContactWest
2​
05/08/2022​
ContactWest
2​
10/08/2022​
ContactWest
2​
13/08/2022​
Non-ContactWest
2​
24/08/2022​
Non-ContactWest
2​
03/09/2022​
ContactWest
2​
05/09/2022​
Non-ContactWest
3​
14/08/2022​
ContactEast
3​
22/08/2022​
ContactEast
3​
26/08/2022​
ContactEast
3​
27/08/2022​
Non-ContactEast
3​
29/08/2022​
Non-ContactEast

I want to calculate number of unique clients by month and year split by area and only where call type is Contact so my expected results would be:

Aug-23​
Sep-23​
East
2​
1​
West
1​
1​

How can i achieve this? I would also like to mention, my table is on 1 tab and my calculations on a separate tab.

Grateful to anyone who i able to answer my query.

kind regards
Hetal
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
Fluff said:
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
Click to expand...
I am using Excel for Microsoft 365
 
Upvote 0
Thanks for that.
How about
Fluff.xlsm
ABCD
1Client IDDateCall TypeArea
2101/08/2022ContactEast
3104/08/2022ContactEast
4109/08/2022ContactEast
5115/08/2022Non-ContactEast
6102/09/2022ContactEast
7106/09/2022ContactEast
8110/09/2022Non-ContactEast
9119/09/2022Non-ContactEast
10126/09/2022ContactEast
11202/08/2022Non-ContactWest
12205/08/2022ContactWest
13210/08/2022ContactWest
14213/08/2022Non-ContactWest
15224/08/2022Non-ContactWest
16203/09/2022ContactWest
17205/09/2022Non-ContactWest
18314/08/2022ContactEast
19322/08/2022ContactEast
20326/08/2022ContactEast
21327/08/2022Non-ContactEast
22329/08/2022Non-ContactEast
Sheet1


Fluff.xlsm
ABC
101/08/202201/09/2022
2East21
3West11
Sheet2
Cell Formulas
RangeFormula
B2:C3B2=ROWS(UNIQUE(FILTER(Sheet1!$A$2:$A$100,(Sheet1!$C$2:$C$100="Contact")*(Sheet1!$D$2:$D$100=$A2)*(Sheet1!$B$2:$B$100>=B$1)*(Sheet1!$B$2:$B$100<=EOMONTH(B$1,0)))))
 
Upvote 0
Fluff said:
Thanks for that.
How about
Fluff.xlsm
ABCD
1Client IDDateCall TypeArea
2101/08/2022ContactEast
3104/08/2022ContactEast
4109/08/2022ContactEast
5115/08/2022Non-ContactEast
6102/09/2022ContactEast
7106/09/2022ContactEast
8110/09/2022Non-ContactEast
9119/09/2022Non-ContactEast
10126/09/2022ContactEast
11202/08/2022Non-ContactWest
12205/08/2022ContactWest
13210/08/2022ContactWest
14213/08/2022Non-ContactWest
15224/08/2022Non-ContactWest
16203/09/2022ContactWest
17205/09/2022Non-ContactWest
18314/08/2022ContactEast
19322/08/2022ContactEast
20326/08/2022ContactEast
21327/08/2022Non-ContactEast
22329/08/2022Non-ContactEast
Sheet1


Fluff.xlsm
ABC
101/08/202201/09/2022
2East21
3West11
Sheet2
Cell Formulas
RangeFormula
B2:C3B2=ROWS(UNIQUE(FILTER(Sheet1!$A$2:$A$100,(Sheet1!$C$2:$C$100="Contact")*(Sheet1!$D$2:$D$100=$A2)*(Sheet1!$B$2:$B$100>=B$1)*(Sheet1!$B$2:$B$100<=EOMONTH(B$1,0)))))
Click to expand...
Thank you very much.

Works as i expect it to. very much appreciated
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
suggest excel formula to calculate cumulative interest on no of days etc ......
Replies
1
Views
664
Peter_SSs
Peter_SSs
G
  • Question Question
Formula to Retrieve uniqueID row Above
Replies
1
Views
506
Trebor76
T
G
  • Solved
Splitting Dates with Unique ID
Replies
6
Views
565
Akuini
Akuini
C
  • Solved
Extract Dates from 2 columns
Replies
2
Views
655
Peter_SSs
Peter_SSs
staticfluids
  • Question Question
Distribute Number into Several Part (Increment) Based on Other Data (Dynamic Array)
Replies
2
Views
397
staticfluids
staticfluids

Forum statistics

Threads
1,224,813
Messages
6,181,117
Members
453,021
Latest member
Justyna P

Share this page

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