How to Count Cells 10 Days Before Today

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.

Leave a Reply