Practice Workbook
Download the following workbook and exercise.
The Excel COUNTIF Function
The COUNTIF function counts the cells in an array or range that match a single criterion.
- Syntax:
=COUNTIF (range, criteria)
- Arguments:
range: The cell range to count.
criteria: Single criterion applied to the range.
The Excel COUNTIFS Function
The COUNTIFS function counts the cells in an array or range that match multiple criteria.
- Syntax:
=COUNTIFS (criteria_range1, criteria1, [criteria_range2, criteria2], ..)
- Arguments:
criteria_range1: The first cell range to count.
criteria1: Criteria applied to criteria_range1.
- Optional Arguments:
criteria_range2: The second cell range to count.
criteria2: Criteria applied to criteria_range2.
Method 1 – Counting If the Date is within 7 Days using the Excel COUNTIF Function
This is the sample dataset.
STEPS:
- Select E8.
- Enter the formula:
=COUNTIFS(C5:C11,">"&TODAY()-E5,B5:B11,F5)
- Press Enter to see the result.
Formula Breakdown
TODAY()
returns the current date.
TODAY()-E5
subtracts ‘7’ from the current day.
C5:C11,”>”&TODAY()-E5
returns the total number of days greater than or equal to the returned date.
B5:B11,F5
returns the total number of ‘BKD’ items.
COUNTIFS(C5:C11,”>”&TODAY()-E5,B5:B11,F5)
counts the total number of cells.
Read More: Excel COUNTIF with Greater Than and Less Than Criteria
Method 2 – Using the Excel COUNTIF Function to Count a Date within 7 Days Excluding Future Dates
STEPS:
- Select E7.
- Enter the formula:
=COUNTIF(C:C,">="&TODAY()-7)-COUNTIF(C:C,">"&TODAY())
- Press Enter to see the result.
Formula Breakdown
TODAY()
returns the current date.
TODAY()-7
subtracts ‘7’ from the current day.
COUNTIF(C:C,”>=”&TODAY()-7)
returns the total number of days greater than or equal to the returned date.
COUNTIF(C:C,”>”&TODAY())
returns the total number of future dates.
COUNTIF(C:C,”>=”&TODAY()-7)-COUNTIF(C:C,”>”&TODAY())
returns the subtracted value.
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 the COUNTIF Function to Find If a Date is within 7 Days
STEPS:
- Select E8.
- Enter the formula:
=SUMPRODUCT(--($C$5:$C$11>TODAY()-$E$5),--($B$5:$B$11=$F$5))
- Press Enter to see the result.
Formula Breakdown
TODAY()
returns the current date.
TODAY()-$E$5
subtracts ‘7’ from the current day.
–($C$5:$C$11>TODAY()-$E$5)
is the first array. ‘—’ converts a series of TRUE and FALSE into ‘0’ or ‘1’ in the array. It will return the total number of days greater than the returned date.
–($B$5:$B$11=$F$5)
is the second array. ‘—’ functions as described above. It returns the total number of ‘CKD’ items.
SUMPRODUCT(–($C$5:$C$11>TODAY()-$E$5),–($B$5:$B$11=$F$5))
sums the amounts found from the above procedure.
Read More: COUNTIF Between Two Dates in Excel (4 Suitable Examples)
Related Readings
- COUNTIF Excel Example (22 Examples)
- How to Use IF and COUNTIF Functions Together in Excel
- Apply COUNTIF Function in Multiple Ranges for Same Criteria
- COUNTIF Function to Count Cells That Are Not Equal to Zero
- How to Apply COUNTIF Between Two Cell Values in Excel
