Count Weekdays and Weekends Without Blank Cells in Excel
Question details
Count dates that fall on weekdays or weekends in a date range while excluding blank cells.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs formulas to calculate the number of weekday or weekend entries in a date range that may contain cleared or empty cells.
- Observed behavior
- Blank cells can be evaluated as date serial values by date formulas, causing them to be counted as weekends unless a nonblank condition is included.
Confirm that the populated cells in the range contain real Excel dates rather than date-looking text. Replace date_range in the formulas with your actual range, such as A2:A100.
Use SUMPRODUCT to count weekend dates and ignore blank cells
Use this formula when you want to count Saturdays and Sundays in a date range without including empty cells.
The date_range<>"" test excludes blank cells before WEEKDAY evaluates the date. With WEEKDAY(date_range,2), Monday is 1 and Sunday is 7, so Saturday and Sunday are values 6 and 7.
Click the cell where you want the weekend total to appear, such as B2.
Type =SUMPRODUCT((A2:A100<>"")*(WEEKDAY(A2:A100,2)>=6)) and replace A2:A100 with your date range.
Press Enter to calculate the total. Only nonblank dates that fall on Saturday or Sunday are counted.

Use SUMPRODUCT to count weekday dates and ignore blank cells
Use this version when you need to count Monday through Friday dates while leaving blank cells out of the result.
Count Nonblank Weekdays and Weekends in WPS Spreadsheets
WPS Spreadsheets supports SUMPRODUCT and WEEKDAY formulas, so you can count weekday or weekend dates while excluding blank cells with the same familiar worksheet workflow.
- 1. Open the date worksheet: Open your .xlsx or .xls file in WPS Spreadsheets and identify the cells containing the dates, such as A2:A100.
- 2. Choose a result cell: Click an empty cell where you want to display the weekday or weekend total.
- 3. Enter the required formula: For weekends, enter =SUMPRODUCT((A2:A100<>"")*(WEEKDAY(A2:A100,2)>=6)). For weekdays, change >=6 to <=5.
- 4. Confirm the calculation: Press Enter and verify that empty cells are not included in the displayed total.

Frequently Asked Questions
Why are blank cells counted as weekends in my date formula?
A blank cell may be evaluated as date serial number 0 by a date function, and that value can map to a weekend day. Add a nonblank test such as (A2:A100<>"") before the WEEKDAY condition.
How do I count only Saturdays and Sundays in Excel?
Use =SUMPRODUCT((A2:A100<>"")*(WEEKDAY(A2:A100,2)>=6)). The return_type argument 2 makes Saturday 6 and Sunday 7.
What formula counts Monday through Friday while ignoring empty cells?
Use =SUMPRODUCT((A2:A100<>"")*(WEEKDAY(A2:A100,2)<=5)). With WEEKDAY(range,2), Monday through Friday are numbered 1 through 5.
Can I use a named range instead of A2:A100?
Yes. If your named range is Dates, use =SUMPRODUCT((Dates<>"")*(WEEKDAY(Dates,2)>=6)) for weekends or change the final test to <=5 for weekdays.
Will these formulas work in WPS Spreadsheets?
Yes. WPS Spreadsheets supports SUMPRODUCT and WEEKDAY and can use the same formulas in compatible .xlsx and .xls workbooks.




