You can count the number of cells 10 days before a date with the COUNTIF function or the SUMPRODUCT function.
Example: You are working on a worksheet with the first name in column A, the last name in column B, and the date of appointment in column C.
Formula 1: How many people's appointments are ten days before 12/15/2022?
=COUNTIF(C2:C12, "<"&"12/15/2022"-10)
The result returns 7, so seven people's appointments are ten days before 12/15/2022.
You may need to change the date format depending on your computer settings, such as "2022-12-15".
Explanation:
- Step 1: ("12/15/2022"-10): To have a date of ten days before 12/15/2022, which is 12/05/2022;
- Step 2: COUNTIF(C2:C12, "<"&"12/15/2022"-10): To count cells before 12/05/2022.
Formula 2: Alternatively, you can use the SUMPRODUCT function to have the same results.
=SUMPRODUCT(–(C2:C12<"12/15/2022"-10))
=SUMPRODUCT((C2:C12<"12/15/2022"-10)*1)
Explanation:
- Step 1: ("12/15/2022"-10): To have a date of ten days before 12/15/2022, which is 12/05/2022;
- Step 2: (C2:C12<"12/15/2022"-10): If a date earlier than 12/05/2022, the result returns true; otherwise it returns false;
- Step 3: The hyphen (or times 1) 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 then add.
– Array2 is optional; the second array is to multiply and then add.