You can count the number of cells before a weekday (e.g., Thursday) in a period using the SUMPRODUCT function.
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 before Thursday in 2022?
=SUMPRODUCT(–(WEEKDAY(C2:C12) < 5), –(YEAR(C2:C12) = 2022))
=SUMPRODUCT((WEEKDAY(C2:C12) < 5)*1, (YEAR(C2:C12) = 2022)*1)
The result returns 3, so three appointments are before 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)<5): The result returns TRUE if a date is earlier than Thursday; 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 double 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.
