How to count all Sundays in Column A with running dates in sequence.

[Shananarocks]

This may look silly but seems that I can do VBA programming in MS Access but do not know remember how to use Excel formula.. Need answer quick. I thought of using Countif or SumProduct but have errors.

 

[etaf]

theres a few way - detailed here

Count Sundays in a date range

Hi, I have a date range which is 7/21/2009 6:46:44 PM and 11/30/2009 6:06:00 PM, I want to count how many Sundays are there in this date range. Likewise I want to calculate mondays, tuesdays...etc. Can anyone help me in this?

www.mrexcel.com

There are probably lots of ways to do that. Here's one

=SUMPRODUCT(--(WEEKDAY(your_range)=1))

Sunday = 1
Monday = 2 etc

Hope it helps

Lee

Book9
	A	B	C	D	E
1
2	1/1/24	Monday			There are probably lots of ways to do that. Here's one
3	1/2/24	Tuesday
4	1/3/24	Wednesday			3
5	1/4/24	Thursday
6	1/5/24	Friday			Sunday = 1
7	1/6/24	Saturday			Monday = 2 etc
8	1/7/24	Sunday
9	1/8/24	Monday			Hope it helps
10	1/9/24	Tuesday
11	1/10/24	Wednesday			Lee
12	1/11/24	Thursday
13	1/12/24	Friday
14	1/13/24	Saturday
15	1/14/24	Sunday
16	1/15/24	Monday
17	1/16/24	Tuesday
18	1/17/24	Wednesday
19	1/18/24	Thursday
20	1/19/24	Friday
21	1/20/24	Saturday
22	1/21/24	Sunday
Sheet1

Cell Formulas
Range	Formula
E4	E4	=SUMPRODUCT(--(WEEKDAY(A1:A31)=1))
B2:B22	B2	=A2

 

[Shananarocks]

theres a few way - detailed here

Count Sundays in a date range

Hi, I have a date range which is 7/21/2009 6:46:44 PM and 11/30/2009 6:06:00 PM, I want to count how many Sundays are there in this date range. Likewise I want to calculate mondays, tuesdays...etc. Can anyone help me in this?

www.mrexcel.com

Book9
	A	B	C	D	E
1
2	1/1/24	Monday			There are probably lots of ways to do that. Here's one
3	1/2/24	Tuesday
4	1/3/24	Wednesday			3
5	1/4/24	Thursday
6	1/5/24	Friday			Sunday = 1
7	1/6/24	Saturday			Monday = 2 etc
8	1/7/24	Sunday
9	1/8/24	Monday			Hope it helps
10	1/9/24	Tuesday
11	1/10/24	Wednesday			Lee
12	1/11/24	Thursday
13	1/12/24	Friday
14	1/13/24	Saturday
15	1/14/24	Sunday
16	1/15/24	Monday
17	1/16/24	Tuesday
18	1/17/24	Wednesday
19	1/18/24	Thursday
20	1/19/24	Friday
21	1/20/24	Saturday
22	1/21/24	Sunday
Sheet1

Cell Formulas
Range	Formula
E4	E4	=SUMPRODUCT(--(WEEKDAY(A1:A31)=1))
B2:B22	B2	=A2

Thanks for much for the quick reply. The double hyphen syntax really gets me. Appreciate!!!!!

 

[etaf]

Double negatives (--) convert boolean values into binary values. These double negative (--) signs are used to convert TRUE and FALSE values into 1s and 0s. Mostly used with array function and formulas of excel.

McGimpsey & Associates : Excel : Formulae : Why "--"

 

[smozgur]

Thanks for much for the quick reply. The double hyphen syntax really gets me. Appreciate!!!!!

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.