Nuraida Kashmin
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.
STEPS:
- Select Cell E8 at first.
- Next write down the formula:
=COUNTIFS(C5:C11,">"&TODAY()-E5,B5:B11,F5)
- 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.
Read More: Excel COUNTIF with Greater Than and Less Than Criteria
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.
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.
Read More: How to Use COUNTIF to Count Date Less Than Today in Excel
Similar Readings
- How to Use COUNTIF Function in Excel Greater Than Percentage
- COUNTIF vs COUNTIFS in Excel (4 Examples)
- How to Use Excel COUNTIF That Does Not Contain Multiple Criteria
- VBA COUNTIF Function in Excel (6 Examples)
- How to Use Excel COUNTIF Between Time Range (2 Examples)
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.
STEPS:
- First, select Cell E8.
- Now type the formula:
=SUMPRODUCT(--($C$5:$C$11>TODAY()-$E$5),--($B$5:$B$11=$F$5))
- 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.
Read More: COUNTIF Between Two Dates in Excel (4 Suitable Examples)
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.
