Function Problems

Count Weekdays and Weekends Without Blank Cells in Excel

Jason MillerJason Miller Nov 12, 2026 868 views

Question details

Count dates that fall on weekdays or weekends in a date range while excluding blank cells.

Count Weekdays and Weekends Without Blank Cells in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Select a result cell

Click the cell where you want the weekend total to appear, such as B2.

2
Enter the weekend formula

Type =SUMPRODUCT((A2:A100<>"")*(WEEKDAY(A2:A100,2)>=6)) and replace A2:A100 with your date range.

3
Press Enter

Press Enter to calculate the total. Only nonblank dates that fall on Saturday or Sunday are counted.

Use SUMPRODUCT to count weekend dates and ignore blank cells
Blank cells are excluded: The nonblank condition prevents empty cells from being treated as serial date 0, which can otherwise be interpreted as a weekend day.
Count dates accurately in WPS Spreadsheets

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. 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. 2. Choose a result cell: Click an empty cell where you want to display the weekday or weekend total.
  3. 3. Enter the required formula: For weekends, enter =SUMPRODUCT((A2:A100<>"")*(WEEKDAY(A2:A100,2)>=6)). For weekdays, change >=6 to <=5.
  4. 4. Confirm the calculation: Press Enter and verify that empty cells are not included in the displayed total.
Use SUMPRODUCT and WEEKDAY to exclude blank cells from date counts.Count Saturday and Sunday entries or Monday through Friday entries in one formula.Open and edit Microsoft Excel .xlsx and .xls files with compatible formulas.Work in a lightweight spreadsheet interface with familiar formula entry and cell references.
microsoft office alternative - wps office

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.