How to Count Cells Before a Weekday

To count the number of cells before a weekday (e.g., Thursday), you can use the SUMPRODUCT function and the WEEKDAY function. The WEEKDAY function returns the day of the week corresponding to a date.

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?

=SUMPRODUCT(--(WEEKDAY(C2:C12) < 5))

=SUMPRODUCT((WEEKDAY(C2:C12) < 5)*1)

The result returns 6, so six appointments are before Thursday.

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.

Since the formula only counts weekdays, it includes all days before Thursday.

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 before Thursday;  otherwise, it returns FALSE; 
  • Step 3: The double hyphen (or times one) converts TRUE into one and FALSE into zero;
  • Step 4: 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.

Leave a Reply