How to Copy COUNTIF Formulas Across Excel Sheets by Date in WPS Office
Question details
The user needs a COUNTIF formula copied on a Summary sheet to reference different daily worksheet names by date, because ordinary relative references do not automatically increment worksheet names.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is summarizing daily worksheets into a Summary sheet row by row or column by column.
- Observed behavior
- When the COUNTIF formula is copied down, it keeps pointing to the same daily worksheet instead of moving to the next date sheet.
Open the workbook in WPS Spreadsheets and check whether your daily records are in separate tabs or can be combined into one master table. Verify that date values are actual dates and that every daily sheet uses the same column layout.
Consolidate data into a master table and summarize with COUNTIFS or a PivotTable
Use this when you need a stable, non-volatile way to summarize daily worksheets by date and want formulas that copy correctly down and across.
Ordinary cell references change row and column addresses, not worksheet names. A master table with a Date column lets COUNTIFS filter by date and category, so the summary remains reliable as daily data grows.
In WPS Office Spreadsheets, click the plus icon next to the sheet tabs, name the new sheet Master, and copy the daily records into one table with a Date column and the column you want to count.
On the Summary sheet, click the cell where the first daily total should appear, click the Formulas tab, then choose Insert Function, search for COUNTIFS, and build this formula: =COUNTIFS(Master!$A:$A,$A2,Master!$B:$B,B$1).
Press Enter, then drag the fill handle down and right. The locked master range stays fixed while $A2 and B$1 move with each date row and criteria column.
Select the master table, click Insert > PivotTable, place Date in the Rows area and the criteria field in the Columns and Values areas to generate a cross-tab summary by date without copying formulas.
Use INDIRECT with a sheet-name helper list to create dynamic COUNTIF formulas
Use this only if you cannot consolidate the workbook and you accept that INDIRECT formulas are volatile and more difficult to maintain.
Summarize Daily Sheets by Date in WPS Spreadsheets Without Broken COUNTIF References
Use WPS Office Spreadsheets to consolidate daily data into one master table and summarize it by date with COUNTIFS or PivotTables, so copied formulas and reports update correctly as dates change.
- 1. Open the workbook: Launch WPS Office Spreadsheets and open the Excel workbook that contains the daily sheets and Summary sheet.
- 2. Merge daily records into a master table: Insert a new sheet, name it Master, and copy the daily records into one table; include a Date column and the criteria column.
- 3. Create a COUNTIFS summary by date: On the Summary sheet, select the first result cell and enter =COUNTIFS(Master!$A:$A,$A2,Master!$B:$B,B$1), then press Enter.
- 4. Fill the formula across the summary grid: Drag the fill handle down and right to adjust the date row and criteria column references while keeping the master table range locked.
- 5. Check totals: Review a few daily totals against the original sheets to confirm the summary updates correctly as new dated rows are added.
Frequently Asked Questions
Why does my COUNTIF formula keep referencing the same daily worksheet after I copy it?
WPS Spreadsheets follows the same Excel behavior: relative references change cell addresses, not worksheet names. To change the worksheet by date, you need either a helper sheet-name list with INDIRECT or a master data table summarized by COUNTIFS.
Can I use INDIRECT to make COUNTIF use a different sheet name in each row?
Yes. Enter the exact sheet names or matching date values in a helper column, then use an INDIRECT COUNTIF formula that points to that helper cell. Keep in mind that INDIRECT is volatile and can slow large workbooks.
What is the recommended way to summarize daily sheets by date in WPS Office?
Combine the daily records into one master table with a Date column, then use COUNTIFS or a PivotTable. This avoids broken sheet references and updates cleanly when data changes.
Does WPS Office support COUNTIFS and PivotTables like Microsoft Excel?
Yes. WPS Office Spreadsheets opens, edits, and saves .xlsx workbooks and supports COUNTIFS, PivotTables, and other summary formulas, so you can use the same analysis workflow.
How do I create a PivotTable in WPS Spreadsheets to summarize dates?
Select the master data, click Insert > PivotTable, choose a new worksheet, then drag Date to Rows and the criteria field to Columns and Values to create the daily summary.
