Excel formula to count NEW unique names per month

E

esmarques

New Member
Joined
Oct 27, 2020
Messages
37
Office Version
  1. 365
Platform
  1. MacOS
Hello, this one is really hearting my head.
Could any one help me with a formula to count unique names per month in the table below? Data will be added constantly to the table so it would need to count the whole column instead of just a fixed range.
The results would show 2 on cell J2, 1 on cell J3, 3 on cell J4...

I use office 365 on a Mac if this makes any difference.

Any help is welcome... thank you!

Book2
ABCDEFGHIJ
1in device referrer date time name postcodeVisits Per MonthNew Visitors
2in96180.229.9.1664/4/2414:20:49MariaOL5 0QRApr-24
3in101195.11.117.24/4/2413:26:26JonOL5 0QRMay-24
4in96180.229.9.1664/4/2411:03:39JonOL5 0QRJun-24
5in75081.149.159.1515/5/249:52:10Hollyol50dwJul-24
6in75081.149.159.1515/5/249:35:57MariaM4 4EEAug-24
7in75081.149.159.1516/25/249:11:46EraldoSK15 3LHSep-24
8in75081.149.159.1516/25/2419:02:25JonOL5 0QROct-24
9in96180.229.9.1666/25/2411:34:31JaneOL5 0QRNov-24
10in75081.149.159.1516/25/2419:02:25EraldoOL5 0QRDec-24
11in96180.229.9.1666/25/2411:34:31adrianaOL5 0QRJan-25
12Feb-25
13Mar-25
14TOTAL
Sheet1
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
How about
Fluff.xlsm
ABCDEFGHIJ
1in device referrer date time name postcodeVisits Per MonthNew Visitors
2in96180.229.9.16604/04/20240.59778935MariaOL5 0QR01/04/20242
3in101195.11.117.204/04/20240.56002315JonOL5 0QR01/05/20241
4in96180.229.9.16604/04/20240.46086806JonOL5 0QR01/06/20243
5in75081.149.159.15105/05/20240.41122685Hollyol50dw01/07/20240
6in75081.149.159.15105/05/20240.39996528MariaM4 4EE01/08/20240
7in75081.149.159.15125/06/20240.3831713EraldoSK15 3LH01/09/20240
8in75081.149.159.15125/06/20240.79334491JonOL5 0QR01/10/20240
9in96180.229.9.16625/06/20240.48230324JaneOL5 0QR01/11/20240
10in75081.149.159.15125/06/20240.79334491EraldoOL5 0QR01/12/20240
11in96180.229.9.16625/06/20240.48230324adrianaOL5 0QR01/01/20250
1201/02/20250
1301/03/20250
14TOTAL
Data
Cell Formulas
RangeFormula
J2:J13J2=LET(n,$F$2:$F$10000,d,$D$2:$D$10000,IFERROR(ROWS(UNIQUE(FILTER(n,(MONTH(d)=MONTH(I2))*(ISNA(XMATCH(n,SORT(UNIQUE(FILTER(n,MONTH(d)<MONTH(I2)))),,2)))))),0))
 
Upvote 0
Solution
工作簿1.xlsx
ABCDEFGHIJKL
1in device referrer date time name postcodeVisits Per MonthNew Visitors
2in96180.229.9.1662024-4-40.597789352MariaOL5 0QR2024-4-122
3in101195.11.117.22024-4-40.560023148JonOL5 0QR2024-5-111
4in96180.229.9.1662024-4-40.460868056JonOL5 0QR2024-6-133
5in75081.149.159.1512024-5-50.411226852Hollyol50dw2024-7-100
6in75081.149.159.1512024-5-50.399965278MariaM4 4EE2024-8-100
7in75081.149.159.1512024-6-250.383171296EraldoSK15 3LH2024-9-100
8in75081.149.159.1512024-6-250.793344907JonOL5 0QR2024-10-100
9in96180.229.9.1662024-6-250.482303241JaneOL5 0QR2024-11-100
10in75081.149.159.1512024-6-250.793344907EraldoOL5 0QR2024-12-100
11in96180.229.9.1662024-6-250.482303241adrianaOL5 0QR2025-1-100
122025-2-100
132025-3-100
14TOTAL
Sheet1
Cell Formulas
RangeFormula
K2:K13K2=LET(f,F$2:F999,SUM(--FILTER(MATCH(f,f,)=ROW(f)-1,TEXT(D$2:D999,"emm")=TEXT(I2,"emm"),0)))
 
Upvote 0
Fluff said:
How about
Fluff.xlsm
ABCDEFGHIJ
1in device referrer date time name postcodeVisits Per MonthNew Visitors
2in96180.229.9.16604/04/20240.59778935MariaOL5 0QR01/04/20242
3in101195.11.117.204/04/20240.56002315JonOL5 0QR01/05/20241
4in96180.229.9.16604/04/20240.46086806JonOL5 0QR01/06/20243
5in75081.149.159.15105/05/20240.41122685Hollyol50dw01/07/20240
6in75081.149.159.15105/05/20240.39996528MariaM4 4EE01/08/20240
7in75081.149.159.15125/06/20240.3831713EraldoSK15 3LH01/09/20240
8in75081.149.159.15125/06/20240.79334491JonOL5 0QR01/10/20240
9in96180.229.9.16625/06/20240.48230324JaneOL5 0QR01/11/20240
10in75081.149.159.15125/06/20240.79334491EraldoOL5 0QR01/12/20240
11in96180.229.9.16625/06/20240.48230324adrianaOL5 0QR01/01/20250
1201/02/20250
1301/03/20250
14TOTAL
Data
Cell Formulas
RangeFormula
J2:J13J2=LET(n,$F$2:$F$10000,d,$D$2:$D$10000,IFERROR(ROWS(UNIQUE(FILTER(n,(MONTH(d)=MONTH(I2))*(ISNA(XMATCH(n,SORT(UNIQUE(FILTER(n,MONTH(d)<MONTH(I2)))),,2)))))),0))
Click to expand...
Amazing... thank you very much
 
Upvote 0
esmarques,

In the future, when marking a post as the solution, please mark the actual original post containing the solution, not your own reply acknowledging that some other post is the solution.
I have updated it on this thread for you.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Index Match function List based on date
Replies
7
Views
326
Fluff
Fluff
W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
16
Views
2K
phiero21
P
E
  • Solved
Filter and Textjoin formula if two columns match a text value in a cell - excel 365 in a Mac
Replies
7
Views
367
Fluff
Fluff
G
  • Question Question
Formula to unique count with criteria based on a given year
Replies
8
Views
656
goethe168
G
E
  • Solved
extend Excel formula to filter based on a period
Replies
5
Views
290
smozgur
smozgur

Forum statistics

Threads
1,224,820
Messages
6,181,160
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