You can use the SUMPRODUCT function to count the number of cells on weekends that fall in a period (e.g., a year).
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 in 2022?
=SUMPRODUCT(–(WEEKDAY(C2:C12,2) > 5), –(YEAR(C2:C12) = 2022))
=SUMPRODUCT((WEEKDAY(C2:C12,2) > 5)*1, (YEAR(C2:C12) = 2022)*1)
The result returns 3, so three appointments are on weekends (Saturday and Sunday) in 2022.
In this example, Saturday and Sunday are the weekends. However, in some countries or regions, the weekends may be on different days.
The return type for the WEEKDAY function is two, which returns from 1 (Monday) through 7 (Sunday). As a result, Friday returns five.
Explanation:
- Step 1: WEEKDAY(C2:C12, 2): The result returns the day of the week for each date with the return type of two (Monday for one and Sunday for seven);
- Step 2: (WEEKDAY(C2:C12,2) > 5): The result returns TRUE if the date is a Saturday or Sunday; otherwise, it returns FALSE;
- Step 3: (YEAR(C2:C12) = 2022): The result returns TRUE if a date is in 2022; otherwise, it returns FALSE;
- Step 4: The hyphen (or times one) converts TRUE into one and FALSE into zero;
- Step 5: The SUMPRODUCT function returns the sum of the products of the new array.
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.