Function Problems

How to Fill Missing Person and Week Combinations with Zero in Excel

Maira Mehtab Sep 22, 2026 870 views

Question details

The user needs to generate a complete summary table displaying data for every person across specific weeks (e.g., weeks 26 through 52), filling in any missing combinations with a zero rather than leaving them blank.

Product: Excel
Device & OS: not provided
Scenario: Generating a cross-tabular weekly data summary report where all combinations of people and weeks must be explicitly listed, even if there is no recorded action for a specific person in a particular week.
Observed behavior: By default, missing data combinations may not appear or show as blanks, but the user's goal is to explicitly display a zero for these specific empty intersections.

Before you start

Ensure your source data is organized in a flat tabular format with dedicated columns for names, week numbers, and the numerical values you intend to sum.

Solution 1Recommended

Use the SUMIFS Function to Fill Missing Combinations

Create a cross-tabular grid and use the SUMIFS function to pull data. The SUMIFS function natively returns a zero when no criteria matches are found.

A formula-based approach provides a dynamic table that automatically updates when your source data changes. By setting up unique names in rows and weeks in columns, SUMIFS evaluates each combination and outputs a zero for missing entries.

Create a table shell

List unique names down a column (e.g., Column F) and sequential week numbers across a row (e.g., Row 2, starting from G2).

Enter the SUMIFS formula

In the first empty cell of your grid (e.g., G3), enter the formula: =SUMIFS($C:$C, $A:$A, $F3, $B:$B, G$2). Adjust the references so that $C:$C points to your values, $A:$A points to the names column, and $B:$B points to the weeks column in your source data.

Apply the formula across the grid

Select cell G3, click and drag the fill handle across the columns for all weeks, and then drag it down to cover all rows with names. Missing combinations will automatically display as 0.

Absolute and Relative References: Pay close attention to the dollar signs ($) in the formula. Locking the columns for source data ($C:$C) and appropriately locking the row for weeks (G$2) and column for names ($F3) is crucial for the formula to calculate correctly when copied.

Solution 2

Create a Complete Summary Table using a PivotTable

PivotTables can automatically cross-tabulate your data and offer a built-in option to display zeros for empty cells instead of blanks.

Efficient Data Summarization

Easily Manage and Summarize Data with WPS Spreadsheet

WPS Spreadsheet offers powerful functions like SUMIFS and intuitive PivotTable features to quickly summarize your weekly data. You can effortlessly fill missing combinations with zeros and generate professional reports in seconds.

1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your source data.
2. Extract unique values: Use the 'Remove Duplicates' feature under the Data tab to quickly generate a list of unique names and weeks for your summary table headers.
3. Apply SUMIFS or a PivotTable: Input the =SUMIFS() formula into your cross-tabular grid, or go to the Insert tab to create a PivotTable.
4. Format missing data: If using a PivotTable, right-click to access Options and set empty cells to display '0', completing your report.

Fully compatible with Microsoft Excel file formats (.xlsx and .xls).Supports advanced data functions including SUMIFS, COUNTIFS, and array formulas.Intuitive PivotTable interface for quick cross-tabulation and custom formatting.Lightweight application with fast processing for large datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my SUMIFS formula return an error instead of zero?

This typically happens if the range sizes in your SUMIFS formula do not match perfectly. Ensure that the sum_range and all criteria_ranges cover the exact same number of rows (e.g., $C$2:$C$100, $A$2:$A$100, and $B$2:$B$100). If you reference entire columns like $C:$C, make sure all references in the formula use entire columns.

Can I use Power Query to fill missing combinations with zeros?

Yes. You can load your data into Power Query, group the data by Name and Week, and then pivot the Week column. During the pivot operation, select your value column and choose 'Sum' in the advanced options. Afterward, select all the week columns, right-click, choose 'Replace Values', and replace 'null' with '0'.

How do I quickly extract unique names for my formula-based table?

Copy your entire list of names from the source data to a new column. Select the copied data, navigate to the Data tab on the ribbon, and click on 'Remove Duplicates'. This will leave you with a clean list of unique names to use as your row headers.

Excel & Data Problems