Apply a Formula to the First Repeated Value in an Excel PivotTable
Question details
The user wants a formula to apply only to the first occurrence of a repeated value in a PivotTable.

- Product
- Microsoft Excel PivotTable
- Device & OS
- not provided
- Scenario
- Repeated source values are summarized in a PivotTable, but the intended calculation should be included only once.
- Observed behavior
- The desired result is a PivotTable calculation that includes the first occurrence and excludes later duplicates.
Identify the column that defines a duplicate, the value to calculate, and the expected result for a small sample. Check the source row order: the helper formula below treats the first source row containing a value as its first occurrence.
Use a Helper Column to Include the First Occurrence
Use this approach when you want to sum a numeric value only once for each repeated key.
This example assumes nonblank keys in column A, numeric values in column B, and headers in row 1. It identifies the first occurrence across the entire source list, not the first visible row in each PivotTable group.
In the PivotTable source data, add a column named First Value. In its row 2 cell, enter =IF(COUNTIF($A$2:A2,A2)=1,B2,0).
Drag the fill handle down the First Value column. The first row for each key returns its column B value; subsequent rows return zero.
Click the PivotTable and choose PivotTable Analyze > Change Data Source. If necessary, expand the source range to include First Value, then click OK.
Right-click the PivotTable and select Refresh. In the PivotTable Fields pane, drag First Value to Values and set Value Field Settings to Sum if needed.

Calculate First Occurrences in WPS Spreadsheet
Add a helper formula to your source data in WPS Spreadsheet, then use a PivotTable to sum the first value for each repeated key.
- 1. Open your source data: Open the workbook in WPS Spreadsheet and identify the key and numeric value columns.
- 2. Create the helper formula: Add a First Value column. If keys are in A and values are in B, enter =IF(COUNTIF($A$2:A2,A2)=1,B2,0) in row 2 and fill it down.
- 3. Create a PivotTable: Select the source data including First Value, then choose Insert > PivotTable and select a location for the report.
- 4. Summarize the results: In the PivotTable field list, add the key to Rows and First Value to Values. Set the value summary to Sum if necessary.

Frequently Asked Questions
Why does the formula use a helper column instead of a calculated field?
The helper column checks the source rows in order with COUNTIF. A standard PivotTable calculated field works with PivotTable fields and does not identify the first source-row occurrence in the same way.
What happens if I sort the source data?
The first occurrence may change because the formula checks rows from A2 through the current row. After sorting the source, right-click the PivotTable and select Refresh.
How can I count each key once instead of summing its value?
In the helper column, use =IF(COUNTIF($A$2:A2,A2)=1,1,0), fill it down, and add the field to PivotTable Values as a Sum.
Can the formula identify the first occurrence within each group?
The example checks column A across the whole source list. To restart the count for each group, use a helper formula with criteria for both the group and the repeated key, then refresh the PivotTable.




