You can count the number of cells between weekdays in a period (e.g., year) using the WEEKDAY and SUMPRODUCT functions.
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 people have appointments from Tuesday to Thursday in 2022?
=SUMPRODUCT(–(WEEKDAY(C2:C12) >= 3), –(WEEKDAY(C2:C12) <= 5), –(YEAR(C2:C12) = 2022))
=SUMPRODUCT((WEEKDAY(C2:C12) >= 3)*1, (WEEKDAY(C2:C12) <= 5)*1, (YEAR(C2:C12) = 2022)*1)
The result returns 4, so four appointments are from Tuesday to Thursday in 2022.
Please note that the formula uses the default return type for the WEEKDAY function, which returns from 1 (Sunday) to 7 (Saturday). As a result, Thursday returns 5. The weekdays before Thursday are Sunday, Monday, Tuesday, and Wednesday.
Explanation:
- Step 1: WEEKDAY(C2:C12): The result returns the day of the week for each date;
- Step 2: (WEEKDAY(C2:C12) >= 2): The result returns TRUE if a date is Tuesday or after; otherwise, it returns FALSE;
- Step 3: (WEEKDAY(C2:C12) <= 5): The result returns TRUE if a date is Thursday or before; otherwise, it returns FALSE;
- Step 4: (YEAR(C2:C12) = 2022): The result returns TRUE if a date is in 2022; otherwise, it returns FALSE;
- Step 5: The double hyphen (or times one) converts TRUE into one and FALSE into zero;
- Step 6: 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.