Site icon ExcelNotes

How to Count Cells on Weekends

You can use the WEEKDAY and SUMPRODUCT functions to count the number of cells on weekends.

Example: You are working with a worksheet with the first name in column A, the last name in column B, the date of appointment in column C, and weekdays in column D.

Question: How many appointments are on weekends?

=SUMPRODUCT(–(WEEKDAY(C2:C12,2) > 5))

=SUMPRODUCT((WEEKDAY(C2:C12,2)*1 > 5))

The result returns 5, so five appointments are on weekends (Saturday and Sunday). 

In this example, Saturday and Sunday are weekends, although some countries and regions may have different weekends.

The return type for the WEEKDAY function is two, which returns from 1 (Monday) through 7 (Sunday). As a result, Friday returns five.

Explanation: 

Notes: The SUMPRODUCT function

The SUMPRODUCT function adds all the multiplication results for all arrays.

Formula:

=SUMPRODUCT(array1, [array2], …)

Explanations:

– Array1 is required; the first array is to multiply and add.
– Array2 is optional; the second array is to multiply and add.

Exit mobile version