How to Group and Total Excel Data by Date Without Power Query in WPS Office
Question details
The user needs to summarize multiple values for each date in an Excel table using built-in spreadsheet methods—dynamic-array formulas, SUMIFS, or a PivotTable—without Power Query.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a table with a Date column and one or more value columns and wants date-wise grouped totals.
- Observed behavior
- The expected result is one total row per date, with values aggregated across the relevant columns.
Confirm that the date column contains real date values rather than text, and verify your actual table or range name if the examples use Table1. If the date column is formatted as text, convert it to Date format before grouping.
Use a Dynamic Array Formula with LET, UNIQUE, FILTER, BYROW, and LAMBDA
Best when the number of value columns is flexible or unknown; the formula spills date-wise totals automatically.
This method mirrors the accepted Excel dynamic-array approach. It creates a distinct date list, totals all remaining columns for each date, and stacks the date column next to the totals. Replace Table1 with your actual table or range name.
Click an empty cell on a blank worksheet area where you want the grouped totals to start.
Type =LET(distinctDates,UNIQUE(Table1[Date]),totalsByDate,BYROW(distinctDates,LAMBDA(date,SUM(FILTER(DROP(Table1,,1),Table1[Date]=date)))),HSTACK(distinctDates,totalsByDate)) and press Enter.
Confirm that the formula returns one distinct date per row and the summed totals for all remaining value columns.
Combine SUMIFS Formulas for a Small Fixed Number of Value Columns
Use when your table has a small, fixed number of numeric columns and you want a straightforward formula result.
Create a PivotTable to Group and Sum by Date
Use when you prefer a no-formula, interactive solution and need date grouping quickly.
Group and Total Excel Data by Date in WPS Office Without Power Query
Open your Excel workbook in WPS Office Spreadsheets and use built-in dynamic array formulas, SUMIFS, or a PivotTable to summarize daily totals without Power Query while keeping full .xlsx compatibility.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office, open your Excel file, and make sure your data is in an Excel Table or a named range.
- 2. Choose a grouping method: Use the LET/BYROW/FILTER formula for flexible columns, SUMIFS for fixed columns, or Insert > PivotTable for a no-formula approach.
- 3. Enter the formula or build the PivotTable: Click an empty output cell and enter the formula, or select your table and choose Insert > PivotTable, then drag Date to Rows and value columns to Values.
- 4. Check and save: Review the grouped totals and save the workbook in .xlsx format, which remains compatible with Microsoft Excel.
Frequently Asked Questions
Can I total data by date in Excel without Power Query?
Yes. Use a PivotTable, SUMIFS formulas, or dynamic-array formulas with UNIQUE, FILTER, BYROW, LAMBDA, and HSTACK in WPS Office Spreadsheets.
What formula automatically handles multiple value columns?
Use =LET(distinctDates,UNIQUE(Table1[Date]),totalsByDate,BYROW(distinctDates,LAMBDA(date,SUM(FILTER(DROP(Table1,,1),Table1[Date]=date)))),HSTACK(distinctDates,totalsByDate)). It drops the date column and sums the rest.
What if my date column is stored as text?
Convert it to real dates first. Select the column, use Home > Number Format or Data > Text to Columns, and confirm the cells are in Date format before grouping.
Can I add a grand total row to the SUMIFS result?
Yes. Wrap the grouped result with VSTACK and append a row created with HSTACK("Grand Total",SUM(type1Grouped,type2Grouped)).
Which method is best if I want a no-formula solution?
Select any cell in the table, click Insert > PivotTable, drag Date to Rows and numeric columns to Values, and set the value field to Sum.
