Rolling Distinct Count by Month YTD

M

Mengli

New Member
Joined
May 24, 2024
Messages
5
Office Version
  1. 365
Platform
  1. Windows
I have a list of sales data that I am attempting to create a distinct count of customers with orders on a YTD basis. I had tried to use distinct count in a pivot table but this gives distinct count each month rather than Feb YTD distinct count. If Customer A has orders in Jan, Feb and March, I don't want them counted 3 times in the March customer count. You can see in the example, order count is distinct from the customers on a YTD basis.

Book2
ABCDEFGHI
1Salesperson nameSold DateCustomer JanFebMarApr
2Donald Trump1/4/2024Customer KUnique Customers10172225
3George Bush1/9/2024Customer LTotal Orders YTD10212834
4Joe Biden1/10/2024Customer M
5Barak Obama1/11/2024Customer N
6Donald Trump1/12/2024Customer O
7George Bush1/12/2024Customer P
8Joe Biden1/17/2024Customer Q
9Barak Obama1/23/2024Customer A
10Barak Obama1/24/2024Customer R
11Donald Trump1/25/2024Customer B
12Donald Trump2/12/2024Customer P
13George Bush2/12/2024Customer R
14Joe Biden2/13/2024Customer D
15Barak Obama2/13/2024Customer W
16George Bush2/14/2024Customer C
17Joe Biden2/14/2024Customer D
18Donald Trump2/20/2024Customer W
19Barak Obama2/22/2024Customer T
20Barak Obama2/26/2024Customer E
21Donald Trump2/27/2024Customer F
22Donald Trump2/27/2024Customer Y
23George Bush3/4/2024Customer I
24Joe Biden3/8/2024Customer S
25Barak Obama3/8/2024Customer T
26Donald Trump3/15/2024Customer U
27George Bush3/21/2024Customer V
28Barak Obama3/26/2024Customer W
29Donald Trump3/29/2024Customer X
30George Bush4/3/2024Customer Y
31Joe Biden4/4/2024Customer Z
32George Bush4/10/2024Customer W
33Joe Biden4/11/2024Customer H
34Barak Obama4/16/2024Customer I
35Barak Obama4/18/2024Customer J
Sheet1
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
I turned the header in F1 to I1 into dates:

MrExcelPlayground23.xlsx
ABCDEFGHI
1Salesperson nameSold DateCustomer JanFebMarApr
2Donald Trump1/4/2024Customer KUnique Customers10172225
3George Bush1/9/2024Customer LTotal Orders YTD10212834
4Joe Biden1/10/2024Customer M
5Barak Obama1/11/2024Customer N
6Donald Trump1/12/2024Customer O
7George Bush1/12/2024Customer P
8Joe Biden1/17/2024Customer Q
9Barak Obama1/23/2024Customer A
10Barak Obama1/24/2024Customer R
11Donald Trump1/25/2024Customer B
12Donald Trump2/12/2024Customer P
13George Bush2/12/2024Customer R
14Joe Biden2/13/2024Customer D
15Barak Obama2/13/2024Customer W
16George Bush2/14/2024Customer C
17Joe Biden2/14/2024Customer D
18Donald Trump2/20/2024Customer WJanFebMarApr
19Barak Obama2/22/2024Customer TUnique Customers10172225
20Barak Obama2/26/2024Customer ETotal Orders YTD10212834
21Donald Trump2/27/2024Customer F
22Donald Trump2/27/2024Customer Y
23George Bush3/4/2024Customer I
24Joe Biden3/8/2024Customer S
25Barak Obama3/8/2024Customer T
26Donald Trump3/15/2024Customer U
27George Bush3/21/2024Customer V
28Barak Obama3/26/2024Customer W
29Donald Trump3/29/2024Customer X
30George Bush4/3/2024Customer Y
31Joe Biden4/4/2024Customer Z
32George Bush4/10/2024Customer W
33Joe Biden4/11/2024Customer H
34Barak Obama4/16/2024Customer I
35Barak Obama4/18/2024Customer J
Sheet3
Cell Formulas
RangeFormula
G1:I1G1=EOMONTH(F1,0)+1
F2:I2F2=ROWS(UNIQUE(FILTER($C$2:$C$35,MONTH($B$2:$B$35)<=MONTH(F$1))))
F3:I3F3=ROWS(FILTER($C$2:$C$35,MONTH($B$2:$B$35)<=MONTH(F$1)))
 
Upvote 0
Unique.xlsm
ABCDEFGHI
1Salesperson nameSold DateCustomer JanFebMarApr
2Donald Trump04-Jan-24Customer KUnique Customers10172225
3George Bush09-Jan-24Customer LTotal Orders YTD10212834
4Joe Biden10-Jan-24Customer M
5Barak Obama11-Jan-24Customer N
9p
Cell Formulas
RangeFormula
F2:I2F2=COUNTA(UNIQUE(FILTER($C$2:$C$35,MONTH($B$2:$B$35)<=(COLUMN()-5))))
F3:I3F3=SUM(--(MONTH($B$2:$B$35)<=(COLUMN()-5)=TRUE))
 
Upvote 0
Thanks
JamesCanale said:
I turned the header in F1 to I1 into dates:

MrExcelPlayground23.xlsx
ABCDEFGHI
1Salesperson nameSold DateCustomer JanFebMarApr
2Donald Trump1/4/2024Customer KUnique Customers10172225
3George Bush1/9/2024Customer LTotal Orders YTD10212834
4Joe Biden1/10/2024Customer M
5Barak Obama1/11/2024Customer N
6Donald Trump1/12/2024Customer O
7George Bush1/12/2024Customer P
8Joe Biden1/17/2024Customer Q
9Barak Obama1/23/2024Customer A
10Barak Obama1/24/2024Customer R
11Donald Trump1/25/2024Customer B
12Donald Trump2/12/2024Customer P
13George Bush2/12/2024Customer R
14Joe Biden2/13/2024Customer D
15Barak Obama2/13/2024Customer W
16George Bush2/14/2024Customer C
17Joe Biden2/14/2024Customer D
18Donald Trump2/20/2024Customer WJanFebMarApr
19Barak Obama2/22/2024Customer TUnique Customers10172225
20Barak Obama2/26/2024Customer ETotal Orders YTD10212834
21Donald Trump2/27/2024Customer F
22Donald Trump2/27/2024Customer Y
23George Bush3/4/2024Customer I
24Joe Biden3/8/2024Customer S
25Barak Obama3/8/2024Customer T
26Donald Trump3/15/2024Customer U
27George Bush3/21/2024Customer V
28Barak Obama3/26/2024Customer W
29Donald Trump3/29/2024Customer X
30George Bush4/3/2024Customer Y
31Joe Biden4/4/2024Customer Z
32George Bush4/10/2024Customer W
33Joe Biden4/11/2024Customer H
34Barak Obama4/16/2024Customer I
35Barak Obama4/18/2024Customer J
Sheet3
Cell Formulas
RangeFormula
G1:I1G1=EOMONTH(F1,0)+1
F2:I2F2=ROWS(UNIQUE(FILTER($C$2:$C$35,MONTH($B$2:$B$35)<=MONTH(F$1))))
F3:I3F3=ROWS(FILTER($C$2:$C$35,MONTH($B$2:$B$35)<=MONTH(F$1)))
Click to expand...
How would I do this if I needed same unique count but by sales person?
 
Upvote 0
For salespeople list on E5:
Excel Formula: 
=SORT(UNIQUE(A2:A35))

Numbers starting at F5, Range F5:I8:
Excel Formula: 
=ROWS(UNIQUE(FILTER($C$2:$C$35,(MONTH($B$2:$B$35)<=MONTH(F$1))*($A$2:$A$35=$E5))))
 

Attachments

  • Salesperson.PNG
    Salesperson.PNG
    19.8 KB · Views: 3
Upvote 0
Solution
Vogateer said:
For salespeople list on E5:
Excel Formula: 
=SORT(UNIQUE(A2:A35))

Numbers starting at F5, Range F5:I8:
Excel Formula: 
=ROWS(UNIQUE(FILTER($C$2:$C$35,(MONTH($B$2:$B$35)<=MONTH(F$1))*($A$2:$A$35=$E5))))
Click to expand...
Works great - thanks!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Case sensitive distinct count based on multiple criteria
Replies
2
Views
275
shaowu459
shaowu459
ibmy
  • Solved
Count Distinct Value In Row Range
Replies
6
Views
286
Vogateer
V
S
  • Question Question
Distinct date range for users
Replies
4
Views
381
Peter_SSs
Peter_SSs
D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
83
Peter_SSs
Peter_SSs
S
  • Solved
Few problems with dynamic arrays
2
Replies
15
Views
294
djclements
djclements

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

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