How to Use COUNTIFS and SUMIFS with Multiple OR Dates in WPS Spreadsheets
Question details
The user needs to count or sum rows in a table where a customer matches a selected value and a date matches any of several individual date cells, without COUNTIFS/SUMIFS supporting OR criteria directly.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building a summary worksheet with multiple date cells to use as OR criteria.
- Observed behavior
- COUNTIFS and SUMIFS do not accept an OR condition as a single criteria argument, so the formula must combine multiple date matches into one result.
Open the workbook in WPS Spreadsheets and confirm the table name, customer column, date column, and the individual date cells match the ranges used in the formulas. If you plan to use VSTACK, verify that your WPS Office version supports dynamic array functions or use the SUMPRODUCT alternative below.
Use VSTACK with COUNTIFS/SUMIFS and SUM to aggregate OR date matches
Best when your WPS Office or Microsoft 365 version supports VSTACK; it keeps the formula concise and evaluates all listed date cells as an OR condition.
COUNTIFS and SUMIFS normally accept only one criterion per range. VSTACK creates a vertical array of date values, so COUNTIFS or SUMIFS evaluates each date and returns an array of counts or sums. SUM then aggregates them into a single total, and OR returns TRUE if at least one date matches.
Open WPS Spreadsheets and click the cell where you want the order count, box total, or logical test result to appear.
In the formula bar, type =SUM(COUNTIFS(Table1[Customer],selected,Table1[Date],VSTACK(J3,F4,I5,K7,G8,H8))) and press Enter.
For box totals, use =SUM(SUMIFS(Table1[Boxes],Table1[Customer],selected,Table1[Date],VSTACK(J3,F4,I5,K7,G8,H8))) and press Enter.
If you only need to know whether at least one date matches, enter =OR(COUNTIFS(Table1[Customer],selected,Table1[Date],VSTACK(J3,F4,I5,K7,G8,H8))).
Use SUMPRODUCT with OR logic for versions without VSTACK
Use this if your WPS Office build does not support VSTACK or you need a widely compatible Excel formula.
Count and Sum OR Date Matches Directly in WPS Spreadsheets
Use WPS Spreadsheets to apply COUNTIFS, SUMIFS, VSTACK, or SUMPRODUCT formulas that correctly count orders and total boxes when the date must match any of several date cells.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, click Spreadsheets, and open the Excel workbook that contains the table and date criteria.
- 2. Confirm ranges and date cells: Check the table name and the customer, date, and box columns used in the formula to avoid #NAME? or #VALUE! errors.
- 3. Enter the recommended formula: Click the result cell, then type the VSTACK-based COUNTIFS/SUMIFS formula or the SUMPRODUCT alternative from the solutions above.
- 4. Validate the result: Press Enter and change one of the date cells to confirm the count or total recalculates according to the OR date condition.
Frequently Asked Questions
Does WPS Spreadsheets support VSTACK inside COUNTIFS and SUMIFS?
Newer WPS Office builds that include dynamic array functions support VSTACK. If your version returns #NAME?, use the SUMPRODUCT formula in this article, which works without VSTACK.
Why do I need SUM around COUNTIFS for multiple OR dates?
COUNTIFS normally returns one result per criterion combination. With VSTACK as the date criterion, COUNTIFS returns an array of counts for each date. SUM adds those counts into the total for all listed dates.
How do I total the Boxes column instead of counting rows for multiple dates?
Use SUMIFS with the Boxes sum range and the same VSTACK date array, wrapped in SUM: =SUM(SUMIFS(Table1[Boxes],Table1[Customer],selected,Table1[Date],VSTACK(...))).
Can I use this approach if my date cells are not in a continuous range?
Yes. The formulas accept non-contiguous date cells directly, such as VSTACK(J3,F4,I5,K7,G8,H8). You can add or remove date cell references inside VSTACK or inside the SUMPRODUCT OR logic.
