How to Count Cells 10 Days Before a Date

You can count the number of cells 10 days before a date with 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's appointments are 10 days before 12/15/2022?

=COUNTIF(C2:C12, "<"&"12/15/2022"-10)

The result returns 7, so seven people's appointments are 10 days before 12/15/2022.

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, "<"&"12/15/2022"-10): To count cells earlier than 10 days before 12/15/2022.

Formula 2: The COUNTIF function is much easier, but you can also use the PRODUCT function to have the same results.

=SUMPRODUCT(--(C2:C12<"12/15/2022"-10))
=SUMPRODUCT((C2:C12<"12/15/2022"-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<"12/15/2022"-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