count number of unique Mondays in a date column

[smm]

Hoping someone here can help as google doesnt bring up anything

I have a sign-in spreadsheet with date, name, email which is used each Monday and Wednesday . There are several rows with the same Monday date, then several rows with the same Wed date and so on.

Mon 7 Aug, bob smith, email
Mon 7 Aug, sarah brown, email
Mon 7 Aug, neil grey, email
Mon 7 Aug, james white, email
Wed 9 Aug, bob smith, email
Wed 9 Aug, james white, email
Mon 14 Aug, bob smith, email
Mon 14 Aug, sarah brown, email
Mon 14 Aug, neil grey, email

I have a statistics summary at the bottom using SUMPRODUCT and WEEKDAY formulas, so it shows:
DAY, HELPER, COUNT
Sunday, 1, 0
Monday, 2, 7
Tuesday, 3, 0
Wednesday, 4, 2
Thursday, 5, 0
Friday, 6, 0
Saturday, 7, 0

So I know the total attendees on all the Mondays (7) and the total attendees on all the Wednesdays (2)

I'd like to be able to count UNIQUE instances of each weekday, so it would show from the data above that there were 2 sessions on a Monday and 1 session on a Wednesday:

DAY, HELPER, COUNT, UNIQUE COUNT
Sunday, 1, 0, 0
Monday, 2, 7, 2
Tuesday, 3, 0, 0
Wednesday, 4, 2, 1
Thursday, 5, 0, 0
Friday, 6, 0, 0
Saturday, 7, 0, 0

Thank you

 

[AhoyNC]

Try:

Book1
	A	B	C	D	E	F
1	Mon 7 Aug	bob smith	email		Mon	2
2	Mon 7 Aug	sarah brown	email		Wed	1
3	Mon 7 Aug	neil grey	email
4	Mon 7 Aug	james white	email
5	Wed 9 Aug	bob smith	email
6	Wed 9 Aug	james white	email
7	Mon 14 Aug	bob smith	email
8	Mon 14 Aug	sarah brown	email
9	Mon 14 Aug	neil grey	email
Sheet1

Cell Formulas
Range	Formula
F1:F2	F1	=COUNTA(UNIQUE(FILTER($A$1:$A$9,LEFT($A$1:$A$9,3)=E1)))

 

[Dave Patton]

N.B. You can post a concise example of your sheet with the forum's tool named XL2BB.
What is the layout of your data? Are the dates real dates custom formatted to show Mon 7 Aug or just text?

T202308a.xlsm
	A	B	C		E	F	G
1	Dates	Text
2	Mon 7 Aug	Mon 7 Aug	bob smith			Mon	2
3	Mon 7 Aug	Mon 7 Aug	sarah brown			Wed	1
4	Mon 7 Aug	Mon 7 Aug	neil grey
5	Mon 7 Aug	Mon 7 Aug	james white			Mon	2
6	Wed 9 Aug	Wed 9 Aug	bob smith			Wed	1
7	Wed 9 Aug	Wed 9 Aug	james white
8	Mon 14 Aug	Mon 14 Aug	bob smith
9	Mon 14 Aug	Mon 14 Aug	sarah brown
10	Mon 14 Aug	Mon 14 Aug	neil grey
5d

Cell Formulas
Range	Formula
G2:G3	G2	=COUNTA(UNIQUE(FILTER($B$2:$B$10,LEFT($B$2:$B$10,3)=F2)))
G5	G5	=COUNTA(UNIQUE(FILTER($A$2:$A$10,WEEKDAY($A$2:$A$10,2)=1)))
G6	G6	=COUNTA(UNIQUE(FILTER($A$2:$A$10,WEEKDAY($A$2:$A$10,2)=3)))

 

[smm]

Thank you, the dates are real dates in this format “16/08/2023 12:53:39”

 

[Dave Patton]

If the times impact the result, try the following

T202308a.xlsm
	A	B		D	E	F	G
1	Date
2	Mon 7 Aug			email
3	Mon 7 Aug			email
4	Mon 7 Aug			email
5	Mon 7 Aug			email		Mon	2
6	Wed 9 Aug			email		Wed	1
7	Wed 9 Aug			email
8	Mon 14 Aug			email
9	Mon 14 Aug			email
10	Mon 14 Aug			email
5d

Cell Formulas
Range	Formula
G5	G5	=COUNT(UNIQUE(FILTER(INT($A$2:$A$10),WEEKDAY($A$2:$A$10,2)=1)))
G6	G6	=COUNT(UNIQUE(FILTER(INT($A$2:$A$10),WEEKDAY($A$2:$A$10,2)=3)))