How to Count Overlapping Orders by Date in WPS Spreadsheets
Question details
The user needs a spreadsheet formula method to calculate how many orders are active for each date in a range, identify the maximum number of overlapping orders, and count how many dates reach that maximum.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user has a list of order start dates and finish dates and wants to analyze daily overlapping workload or demand.
- Observed behavior
- The user needs a calculated daily active-order count plus two summary values: the maximum overlap and the number of days at that maximum, instead of a pre-existing report.
Make sure your worksheet has valid date values in the start and finish date columns, and create a separate column where the individual evaluation dates will be listed. This prevents text-date mismatches when COUNTIFS compares the ranges.
Count Active Orders per Date with COUNTIFS, MAX, and COUNTIF
Use this method when you have start dates in one column and finish dates in another and need both a daily overlap count and a peak-overlap summary.
This approach works because COUNTIFS checks both conditions for every row: the start date is on or before the evaluation date and the finish date is on or after it.
Enter the first evaluation date in cell F2, then drag the fill handle down to F366 to create the daily date range you want to analyze.
Select G2, type =COUNTIFS($B$2:$B$100,"<="&F2,$C$2:$C$100,">="&F2), and press Enter.
Click the fill handle at the lower-right corner of G2 and drag it down through the last date row to copy the formula next to every date.
In a blank cell, enter =MAX(G2:G366) to return the highest number of overlapping orders.
In another blank cell, enter =COUNTIF(G2:G366,MAX(G2:G366)) to count how many days reached that maximum.
Calculate Overlapping Orders by Date in WPS Spreadsheets
Open your order workbook in WPS Spreadsheets and use the same COUNTIFS, MAX, and COUNTIF formulas to build a daily overlap report, find peak overlapping orders, and count peak days without leaving the spreadsheet.
- 1. Open the workbook: Launch WPS Spreadsheets, click Menu > Open, and select the workbook that contains the order start and finish dates.
- 2. Add the date range and formula: In F2 enter the first evaluation date, fill down to F366, then enter the overlapping-order COUNTIFS formula in G2.
- 3. Calculate peak overlap and day count: Use =MAX(G2:G366) and =COUNTIF(G2:G366,MAX(G2:G366)) in blank cells to return the highest overlap and the number of days at that peak.
- 4. Save as an Excel-compatible workbook: Click Menu > Save As and choose Excel Workbook (*.xlsx) so the formulas remain compatible with Microsoft Excel.
Frequently Asked Questions
Why does COUNTIFS return 0 for a date even though an order overlaps it?
Usually the date is stored as text or one of the columns is text. Select the start and finish date cells, go to Home > Number Format, and choose Date, then re-enter the dates or use Text to Columns to convert them to real dates before using COUNTIFS.
Can I get the maximum overlap without listing every date in a helper column?
The accepted spreadsheet method uses a helper column because COUNTIFS needs a cell date to test against. You can create the date list on a hidden sheet or in a far column, then point MAX and COUNTIF at the helper results.
How do I avoid counting rows with blank start or finish dates?
Filter the data first: select the start and finish date columns, click Home > Sort & Filter > Filter, uncheck (Blanks), and either fill or delete those rows. Then apply the COUNTIFS formula to the cleaned range.
Will these formulas remain intact when I open the workbook in Microsoft Excel?
Yes. WPS Spreadsheets saves ordinary worksheet functions in .xlsx format, so COUNTIFS, MAX, and COUNTIF are preserved and recalculate when the file is opened in Excel.
How can I highlight the dates that reached the maximum overlap?
Select F2:G366, click Home > Conditional Formatting > New Rule, choose Use a formula to determine which cells to format, and enter =$G2=MAX($G$2:$G$366). Then set a highlight format; it will mark every date whose active-order count equals the peak.
