How Many Times A Day Of The Week Appears In A List Of Dates

[excelbytes]

I have a challenge here that I can't figure out the best approach. I have range from A2;A1001. In it dates are added at various times. sometimes multiple times a day, sometimes days are skipped. Ultimately, I need a formula that will count the number of times in that range a unique Monday, Tuedsay, etc. appears. Example, If the date range includes:

8/3/20
8/3/20
8/4/20
8/5/20
8/5/20
8/10/20
8/10/20
8/10/20


The result would be 2 - 8/3 & 8/10.

Alternately, in lieu of a formula, I have VBA code that will create a list of unique dates from that overall list, then format them as Mon, Tue, Wed, etc. However, how do I count the number of times Mon or Tue or Wed appear in this list since the underlying data is really a date, and I'm trying to compare that to the text of Mon, Tue, Wed, etc.?

 

[jasonb75]

Here's one way, there are probably others.

Note that the weekdays in column B are not needed for the formula, I just added them for reference.

The part of the formula WEEKDAY(A1:A8)=2 determines the day of the week to count, 1= Sunday 7 = Saturday.

Book1
	A	B
1	08/03/20	Mon
2	08/03/20	Mon
3	08/04/20	Tue
4	08/05/20	Wed
5	08/05/20	Wed
6	08/10/20	Mon
7	08/10/20	Mon
8	08/10/20	Mon
9		2
Sheet18

Cell Formulas
Range	Formula
B1:B8	B1	=TEXT(A1,"ddd")
B9	B9	=SUM(SIGN(FREQUENCY(IF(WEEKDAY(A1:A8)=2,A1:A8),A1:A8)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[excelbytes]

Perfect! It works great! Thanks so much.