You can count the number of cells 10 days after today using 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' appointments are 10 days after today?
=COUNTIF(C2:C12, ">"&TODAY() + 10)
If today is November 15, 2022, the result returns 4, so four people's appointments are 10 days after today.
Explanation:
Step 1: TODAY(): To have today's date;
Step 2: TODAY() + 10: To have the date of 10 days after today;
Step 3: COUNTIF(C2:C12, ">"&TODAY() + 10): To count cells more than 10 days after today.
The COUNTIF function is much easier, but you can also use the PRODUCT function to have the same results.
Formula 2: How many people' appointments are 10 days after today?
=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 after today;
Step 3: (C2:C12 > TODAY() + 10): If a date more than 10 days after 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.