How to Count Values Below a Limit During the Last Six Months in Excel
Question details
The user needs to count specific numeric values (such as drug levels) that fall below a designated threshold within a rolling six-month timeframe using Excel formulas.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering and counting dataset records based on a dynamic rolling date range and a specific numerical criteria.
- Observed behavior
- To achieve accurate results, a rolling six-month period must be calculated dynamically rather than using a fixed 180-day interval, alongside counting instances that meet the numeric condition.
Ensure that your date column contains valid Excel dates and that the values you want to evaluate are formatted as numbers, rather than text strings.
Use COUNTIFS and EDATE to Count Records
The most accurate way to filter records over a rolling six-month period and apply a numeric threshold is by combining the COUNTIFS, EDATE, and TODAY functions.
The COUNTIFS function allows you to apply multiple criteria to your data. By combining it with EDATE and TODAY, you can dynamically establish a start date exactly six calendar months ago.
Click on the empty cell where you want the final count to be displayed.
Type the formula =COUNTIFS(Table1[Date], ">="&EDATE(TODAY(), -6), Table1[DrugLevel], "<=2.3"). Replace 'Table1[Date]' and 'Table1[DrugLevel]' with your actual cell ranges, and '2.3' with your specific threshold.
Press Enter. The cell will calculate and display the total number of records from the last six months where the value is 2.3 or less.
Troubleshoot Invalid Dates and Text Values
If your formula returns 0 or unexpected results, your dates or numeric values might be stored as text.
Easily Manage Advanced Formulas with WPS Office
WPS Spreadsheet provides seamless compatibility with Excel formulas, including COUNTIFS, EDATE, and TODAY, allowing you to perform complex data analysis efficiently and for free.
- 1. Open your dataset: Launch WPS Spreadsheet and open the file containing your dates and numeric values.
- 2. Input the formula: Select an empty cell and type the exact same =COUNTIFS formula as you would in Microsoft Excel.
- 3. Calculate the result: Press Enter to instantly process your rolling six-month data and view your calculated results.
Frequently Asked Questions
Can I use TODAY()-180 instead of the EDATE function?
While TODAY()-180 works as a rough estimate, it isn't strictly accurate for a six-month period because calendar months vary in length. EDATE(TODAY(), -6) dynamically calculates the exact date six calendar months ago regardless of leap years or 31-day months.
How do I change the formula to count values above the limit instead?
Simply modify the logical operator inside the quotation marks. For example, change "<=2.3" to ">2.3" in the COUNTIFS criteria argument to count values strictly greater than your threshold.
Why is my COUNTIFS formula returning a #VALUE! error?
A #VALUE! error usually occurs if the ranges provided in the COUNTIFS formula are not exactly the same size. Ensure that your date range (e.g., A2:A100) and your numeric value range (e.g., B2:B100) contain the exact same number of rows.
Does WPS Spreadsheet fully support the EDATE function?
Yes, WPS Spreadsheet natively supports the EDATE, TODAY, and COUNTIFS functions. You can use the exact same formulas and syntax as you do in Microsoft Excel without any modifications.
