Calculation Issues

How to Sum Unique Visible Values in a Filtered Excel Column

Ayan Masood Oct 22, 2026 869 views

Question details

The user needs to calculate the sum of specific values (such as a market budget) exactly once, ensuring the total automatically updates when data is filtered.

How to Sum Unique Visible Values in a Filtered Excel Column

Product: Excel
Device & OS: not provided
Scenario: Working with a dataset containing duplicate categories where filters are applied, requiring a dynamic total that ignores both hidden rows and duplicate entries.
Observed behavior: Standard SUM or SUBTOTAL functions either count duplicate values or fail to isolate distinct items among the currently visible rows, resulting in an inaccurate total.

Before you start

Ensure your dataset is organized with clear column headers and no merged cells before applying formulas or converting it to a structured table.

Solution 1Recommended

Use a Helper Column with AGGREGATE and Dynamic Array Formulas

Combine the AGGREGATE function to detect visible rows with UNIQUE and FILTER functions to dynamically sum distinct values.

This method uses a hidden helper column to flag rows that are currently visible. A dynamic array formula then filters the dataset to only include those visible rows, extracts the unique market-budget pairings, and sums the results.

Format data as a Table

Select your dataset and press Ctrl+T to convert it into a structured table. Name the table 'Table1' in the Table Design tab.

Add a visibility helper column

Create a new column named 'Visible'. In the first cell of this column, enter the formula =AGGREGATE(3,5,[@Name]) (assuming 'Name' is an adjacent text column). This will return a 1 for visible rows and 0 for hidden ones.

Enter the dynamic sum formula

Click the cell where you want your final total to appear. Enter the formula: =SUM(UNIQUE(FILTER(Table1[[Market]:[Budget]],Table1[Visible]=1))).

Test the filters

Press Enter to apply the formula. Apply a filter to your table; the total will now automatically update to sum only the unique visible market budgets.

Use a Helper Column with AGGREGATE and Dynamic Array Formulas

Hiding Helper Columns: Once the 'Visible' helper column is set up and working, you can right-click its column letter and select 'Hide' to keep your spreadsheet clean.

Solution 2

Use a COUNTIF Helper Column and a Total Row

A simpler approach utilizing a standard IF and COUNTIF formula to only return a value on its first appearance.

Solution 3

Use Power Pivot and DAX Measures

Ideal for complex dashboards, this uses the Data Model and DAX to sum unique values while interacting with slicers.

Powerful Spreadsheet Tool

Manage and Analyze Filtered Data Easily in WPS Spreadsheet

WPS Office provides a robust Spreadsheet application that fully supports advanced functions like AGGREGATE, UNIQUE, and FILTER, making it simple to calculate complex totals on filtered data without switching software.

1. Open your dataset: Launch WPS Spreadsheet and open the file containing your market and budget data.
2. Format as a table: Select your data range and press Ctrl+T to convert it into a structured table for easier formula referencing.
3. Apply dynamic formulas: Use the AGGREGATE formula in a helper column to track visible rows, then apply the SUM, UNIQUE, and FILTER combination.
4. Filter and analyze: Use the built-in drop-down arrows in the table headers to filter your data and watch your custom totals update instantly.

Fully compatible with Microsoft Excel formulas and functionsSupports dynamic arrays for complex data filtering and sortingLightweight application with a familiar, easy-to-navigate interfaceBuilt-in advanced filtering and data processing tools

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the standard SUBTOTAL function work for summing unique values?

While SUBTOTAL (or AGGREGATE used alone) can successfully ignore hidden rows in a filtered list, it does not inherently remove or ignore duplicate values. It will sum all visible instances, which inflates the total if a market's budget is listed multiple times in the visible data.

Can I hide the helper column after setting it up?

Yes. Once you have created the helper column (e.g., the 'Visible' column using the AGGREGATE function), you can right-click the column header and select 'Hide'. The dynamic formulas will continue to read the data and work perfectly.

Does the UNIQUE function work in older versions of Excel?

The UNIQUE and FILTER functions are dynamic array formulas available only in Microsoft 365, Excel 2021, and modern spreadsheet software like WPS Office. For older spreadsheet software versions, the COUNTIF helper column method is highly recommended.

Excel & Data Problems