You can count the number of cells ten days after a date using the COUNTIF function or the SUMPRODUCT function.
Example: You are working with 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 after 11/15/2022?
=COUNTIF(C2:C12, ">"&"11/15/2022"+10)
The result returns 4, so four people's appointments are ten days after 11/15/2022.
You may need to change the date format depending on your computer settings, such as "2022-11-15".
Explanation:
- Step 1: ("11/15/2022"+10): Ten days after 11/15/2022 is 11/25/2022;
- Step 2: (">"&"11/15/2022"+10): Days after 11/25/2022;
- Step 3: COUNTIF(C2:C12, ">"&"11/15/2022"+10): To count cells after 11/25/2022.
Formula 2: Alternatively, you can use the SUMPRODUCT function to have the same results.
=SUMPRODUCT(–(C2:C12>"11/15/2022"+10))
=SUMPRODUCT((C2:C12>"11/15/2022"+10)*1)
Explanation:
- Step 1: ("11/15/2022" + 10): Ten days after November 15, 2022, which is November 25, 2022;
- Step 2: (C2:C12 > "11/15/2022" + 10): If a date is after November 25, 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.