Site icon ExcelNotes

How to Count Cells After a Weekday in a Period

You can count the number of cells after a weekday (e.g., Thursday) in a period using the SUMPRODUCT and WEEKDAY 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 appointments are after 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 after 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 after Thursday are Friday and Saturday. 

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