To count the number of cells 10 days before, you can use the COUNTIF function or the SUMPRODUCT function. You can change the 10 days into any number of days.
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 10 days before today?
=COUNTIF(C2:C12, "<"&TODAY()-10)
If today is November 15, 2022, the result returns 6, so six people's appointments are 10 days before today.
Explanation:
Step 1: TODAY(): To have today's date;
Step 2: TODAY()-10: To have date of 10 days before today;
Step 3: COUNTIF(C2:C12, "<"&TODAY()-10): To count cells earlier than 10 days before today.
Formula 2: The COUNTIF function is much easier, but you can also use the PRODUCT function to have the same results.
=SUMPRODUCT(–(C2:C12<TODAY()-10))
=SUMPRODUCT((C2:C12<TODAY()-10)*1)
Explanation:
Step 1: TODAY(): To have today's date;
Step 2: TODAY()-10: To have date of 10 days before today;
Step 3: (C2:C12<TODAY()-10): If a date earlier than 10 days before today, the result returns true, otherwise it returns false;
Step 4: The hyphen (or times 1) converts true into 1, and false into 0;
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 then add.
– Array2 is optional, the second array is to multiply and then add.
