COUNTIF and weekdays

Shinano

Board Regular
Joined
Dec 5, 2004
Messages
63
Office Version
  1. 365
Platform
  1. Windows
I have a range A1:A31 listing the weekdays of a month. I need to count weekdays in this range in one cell and weekend days in another cell.

I am trying to come up with a formular using COUNTIF, but for some reason I can not get it right.

Any advice is appreciated.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Multiple countif's

=countif(a:a, "Monday")

=countif(a:a, "Tuesday")etc
 
Upvote 0
If A1:A31 contain dates then you can count weekdays with

=SUMPRODUCT(--(WEEKDAY(A1:A31,2)<6),--(A1:A31<>""))

and the weekends with

=SUMPRODUCT(--(WEEKDAY(A1:A31,2)>=6),--(A1:A31<>""))
 
Upvote 0
Barry,

Is the European version of Excel different than the US version? On our version, the Weekday function returns 1 for Sunday and 7 for Saturday, so the formulas you posted would need some tweaking to work.


EDIT: Barry pointed out to me the second argument of the WEEKDAY fucnion, which accounts for this.
 
Upvote 0
Thank you for the swift reply. I really appreciate it.

On more question, Barry. What are the: -- ?

I am sorry, but it has been some time since I have used Excel and my skills are pretty rusty.

Again, thanks a lot.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

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