Pivot Table Issues

Count Distinct Materials by Type and Calendar Week in Excel

Anum MushtaqAnum Mushtaq Nov 22, 2026 868 views

Question details

Count unique materials for each type and calendar week in a large Excel dataset without a complex array formula.

Count Distinct Materials by Type and Calendar Week in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
The dataset contains Type, CW, and Material columns, and the user wants a distinct material count for each type and calendar week combination.
Observed behavior
An attempted array formula with additional criteria returned a #VALUE! error.
Before you start

Check that the source data has headers for Type, CW, and Material, and that calendar weeks use a consistent format. Decide how blank Type and Material cells should be treated before counting.

Solution 1Recommended

Use an Excel Data Model PivotTable to count distinct materials

Use this method to summarize unique materials by type and calendar week without an array formula.

1
Prepare the source data

Review the Type, CW, and Material columns. If blank Type cells should represent the same category as 0, enter 0 in those cells.

2
Create the PivotTable

Select a cell in the dataset and choose Insert > PivotTable. Select Add this data to the Data Model, choose where to place the PivotTable, and click OK.

3
Arrange the fields

In the PivotTable Fields pane, drag Type to Rows, CW to Columns, and Material to Values.

4
Select Distinct Count

Right-click a value in the PivotTable and choose Summarize Values By > More Options. Select Distinct Count and click OK.

Use an Excel Data Model PivotTable to count distinct materials
Data Model required: Distinct Count is available for PivotTables created with the Data Model. If the option is missing, check whether your Excel version supports the Data Model and recreate the PivotTable with it enabled.
Count unique materials in WPS

Count unique materials with WPS Spreadsheets

In WPS Spreadsheets, create a separate list of unique Type, CW, and Material combinations, then count the remaining materials in a PivotTable.

  1. 1. Copy the relevant columns: Open the workbook in WPS Spreadsheets and copy the Type, CW, and Material columns, including their headers, to a new worksheet.
  2. 2. Handle blank values: Review the copied columns. Replace blank Type cells with 0 only if they belong to the same category, and filter out blank Material cells if they should not be counted.
  3. 3. Remove duplicate combinations: Select the three copied columns and choose Data > Remove Duplicates, or Data > Duplicates > Remove Duplicates. Select Type, CW, and Material as the comparison columns and confirm.
  4. 4. Create a PivotTable: Select the deduplicated list and choose Insert > PivotTable. Choose a location and confirm.
  5. 5. Count the materials: Drag Type to Rows, CW to Columns, and Material to Values. In the value field settings, choose Count if it is not already selected.
Count materials by type and calendar week without an array formulaOpen and edit Excel XLSX and XLS filesUse a familiar spreadsheet interfaceCreate PivotTables from a deduplicated list
microsoft office alternative - wps office

Frequently Asked Questions

Why is Distinct Count missing from my Excel PivotTable?

Distinct Count requires a PivotTable based on the Data Model. Recreate the PivotTable through Insert > PivotTable and select Add this data to the Data Model, if your Excel version supports it.

Do I need to replace blank Type cells with 0?

Only replace blanks with 0 if both represent the same category in your data. Otherwise, leave them separate so the PivotTable does not combine different categories.

How do I update the Excel count after changing the source data?

Right-click the PivotTable and select Refresh. Make sure new rows are included in the PivotTable's source range.

Can I exclude blank materials from the count?

Yes. Filter out blank Material entries before creating the PivotTable, or use a Material filter in the PivotTable to exclude blanks.