You can use the COUNTIFS function or the SUMPRODUCT function to count the number of cells in the last ten days.
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.
Question: How many appointments were in the last ten days?
=COUNTIFS(C2:C12, ">"&TODAY() – 10, C2:C12, "<"&TODAY())
The result returns 2, so two appointments were in the last ten days if today is November 18, 2022. You can change ten days in the formula to any other number of days.
Alternatively, you can use the SUMPRODUCT function to have the same results.
=SUMPRODUCT(–(C2:C12 > TODAY() – 10), –(C2:C12 < TODAY()))
=SUMPRODUCT(((C2:C12 > TODAY() – 10)*1), ((C2:C12 < TODAY())*1)
The above two formulas will return the same results, either using the double hyphen or times one.
Explanation:
- Step 1: (TODAY() – 10): To have the date ten days ago. If today is November 18, 2022, the date ten days ago is November 8, 2022;
- Step 2: (C2:C12 > TODAY() – 10): If a date is after November 8, 2022, it returns TRUE; otherwise, it returns FALSE;
- Step 3: (C2:C12 < TODAY()): If a date is before November 18, 2022, it returns TRUE; otherwise, it returns FALSE;
- Step 4: The hyphen (or times one) converts TRUE into one and FALSE into zero;
- 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 add.
