COUNTIF Date Is within 7 Days

Sometimes we need to count if the date is within 7 days from the dataset for quick calculation. In this case, we need to use some Excel formulas with the COUNTIF function. But the exact use of the COUNTIF function accepts only one argument, so we need the COUNTIFS function here to add multiple criteria to add a date range. In this article, we are going to know how to use these functions with some easy examples and explanations.


Practice Workbook

Download the following workbook and exercise.


Introduction to the Excel COUNTIF Function

Excel COUNTIF function counts the cells in an array or range that matches a single criterion.

  • Syntax:

=COUNTIF (range, criteria)

  • Arguments:

range: The cell range from where we want to count.

criteria: Single criterion that is applied to the range.


Introduction to the Excel COUNTIFS Function

Excel COUNTIFS function counts the cells in an array or range that matches the multiple criteria. It is tagged under Excel Statistical functions

  • Syntax:

=COUNTIFS (criteria_range1, criteria1, [criteria_range2, criteria2], ..)

  • Arguments:

criteria_range1: The first cell range to count.

criteria1: Criteria that is applied on criteria_range1.

  • Optional Arguments:

criteria_range2: The second cell range to count.

criteria2: Criteria that is applied on criteria_range2.


2 Quick Methods to Use COUNTIF Function If Date is within 7 Days

1. Count If Date is within 7 Days with Excel COUNTIF Function

As we know the COUNTIF function accepts only one argument. So we are going to apply the COUNTIFS function with Excel TODAY function. The TODAY function returns the present date as a serial number format in the spreadsheet. It is tagged under Microsoft Excel time and date functions. This function takes no arguments. It updates whenever the spreadsheet is updated.

Let’s say from the dataset (B4:C11) of sold items with dates. The COUNTIFS function will find out the total number of ‘BKD’ items sold within seven days from today. The value will be returned in Cell E8. Here Cell E5 represents the check days and Cell F5 represents the item name.

Count If Date is within 7 Days with Excel COUNTIF Function

STEPS:

  • Select Cell E8 at first.
  • Next write down the formula:
=COUNTIFS(C5:C11,">"&TODAY()-E5,B5:B11,F5)

Count If Date is within 7 Days with Excel COUNTIF Function

  • Now press Enter and we can see the result.

Formula Breakdown

TODAY()

This will return the present date.

TODAY()-E5

This will subtract the check days ‘7’ from the present day.

C5:C11,”>”&TODAY()-E5

This will return the total number of days that is greater than or equal to the date we find from the above procedure.

B5:B11,F5

This will return the total number of ‘BKD’ items.

COUNTIFS(C5:C11,”>”&TODAY()-E5,B5:B11,F5)

This will count the total number of cells.


Similar Readings:


2. Excel COUNTIF Function to Count Date within 7 Days Excluding Future Date

Assuming we have a dataset (B4:C11) of items with dates. We are going to find out the total number of sold items excluding the future date and return the result in Cell E7. For that, we are going to use the COUNTIFS function. Because the COUNTIF function accepts only one argument.

Excel COUNTIF Function to Count Date within 7 Days Excluding Future Date

STEPS:

  • In the beginning, select Cell E7.
  • Type the formula:
=COUNTIF(C:C,">="&TODAY()-7)-COUNTIF(C:C,">"&TODAY())

  • At last, hit Enter and see the result.

Formula Breakdown

TODAY()

This will return the present date.

TODAY()-7

This will subtract the check days ‘7’ from the present day.

COUNTIF(C:C,”>=”&TODAY()-7)

This will return the total number of days that is greater than or equal to the date we find from the above procedure.

COUNTIF(C:C,”>”&TODAY())

This will return the total number of future dates.

COUNTIF(C:C,”>=”&TODAY()-7)-COUNTIF(C:C,”>”&TODAY())

This will return the subtracted value.


Alternative to COUNTIF Function to Count If Date is within 7 Days

We can use the Excel SUMPRODUCT function instead of the COUNTIF function to add a date range. This returns the sum of the values which are multiplied in an exact range or array. It is tagged under Excel Math and Trigonometry functions. In the SUMPRODUCT function, it needs a minimum of two arrays or ranges

Assuming we have a dataset (B4:C11) of sold items with dates. We are going to find out the total number of ‘CKD’ items sold within seven days from today and return the value in Cell E8. Here cell E5 represents the check days and cell F5 represents the item name.

Alternative to COUNTIF Function to Counit If Date is within 7 Days

STEPS:

  • First, select Cell E8.
  • Now type the formula:
=SUMPRODUCT(--($C$5:$C$11>TODAY()-$E$5),--($B$5:$B$11=$F$5))

Alternative to COUNTIF Function to Counit If Date is within 7 Days

  • Then hit Enter to see the result.

Formula Breakdown

TODAY()

This will return the present date.

TODAY()-$E$5

This will subtract the check days ‘7’ from the present day.

–($C$5:$C$11>TODAY()-$E$5)

This will be the first array. We will use ‘’ to convert a series of TRUES and FALSES into ‘0’ or ‘1’ in this array. This will return the total number of days that is greater than the date we find from the above procedure.

–($B$5:$B$11=$F$5)

This will be the second array. Here we also use ‘’ for the same reason as above. This will return the total number of ‘CKD’ items.

SUMPRODUCT(–($C$5:$C$11>TODAY()-$E$5),–($B$5:$B$11=$F$5))

This will sum up the amounts we found from the above procedure.


Conclusion

By using these methods, we can easily use the COUNTIF function to count if the date is within 7 days. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.


Related Readings

Nuraida Kashmin

Nuraida Kashmin

Hi Everyone! I am Nuraida. Cordially welcome you to my profile. I am a Team Leader of Excel and VBA Content Developer in ExcelDemy. Here I will also post articles related to Microsoft Excel. With a strong educational background in Mechanical Engineering through experimental learning from Rajshahi University of Engineering & Technology. Skilled in Microsoft Word, Creative Writing, Microsoft Excel, Project Management, Support to Organize Different Events, Reporting, Monitoring & Documentation, Online Advocacy and Event Management Related to SAP and Youth Leaders.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo