Function Problems

How to Combine SUBTOTAL and SUMPRODUCT for Unique Excel Values

Adam Davis Oct 10, 2026 870 views

Question details

Calculate a sum based on unique values in a specific column while respecting active data filters.

How to Calculate Subtotals for Unique Values in Filtered Excel Lists

Product: Excel
Device & OS: not provided
Scenario: The user needs to sum values in one column that correspond to the first occurrence of unique items in another column, ensuring the result updates dynamically when list filters are applied.
Observed behavior: Standard SUM or SUMPRODUCT functions calculate all visible rows but fail to isolate unique values, requiring a complex nested formula or a helper column to correctly filter and sum unique occurrences.

Before you start

Ensure your data range does not contain any error values (such as #N/A or #VALUE!) before applying array formulas, and verify the exact starting and ending row numbers of your dataset so you can adjust the formula references accurately.

Solution 1Recommended

Use a Nested SUBTOTAL and COUNTIF Array Formula

Apply a dynamically calculating array formula that uses SUBTOTAL, OFFSET, and COUNTIF to sum unique values even when the list is filtered.

This approach avoids altering your spreadsheet structure. It dynamically divides the values by their occurrence count in the visible range, yielding a correct sum for unique entries.

Select the result cell

Click on the cell where you want the final unique subtotal to be displayed.

Enter the nested formula

Type the following formula: =SUBTOTAL(109,OFFSET(N7,ROW(N7:N5810)-MIN(ROW(N7:N5810)),0,1,1)/COUNTIF(B7:B5810,B7:B5810)). Replace N7:N5810 with your numeric values column and B7:B5810 with your criteria (unique identifier) column.

Evaluate as an array formula

If you are using an older version of Excel, press Ctrl + Shift + Enter to execute the formula as an array. In Microsoft 365 or modern spreadsheet apps, simply press Enter.

Use a Nested SUBTOTAL and COUNTIF Array Formula

Performance Tip: Array formulas over very large datasets (e.g., tens of thousands of rows) might temporarily slow down your spreadsheet's calculation speed.

Solution 2

Use a Helper Column with SUMIFS

If complex array formulas return errors or slow down your workbook, add a helper column to flag the first occurrence of each item for a simpler sum calculation.

Advanced Formulas Made Easy

Calculate Complex Arrays Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas, SUBTOTAL, and SUMPRODUCT functions. You can seamlessly calculate unique values in filtered lists with excellent performance and a familiar user interface.

1. Download and install: Download WPS Office for free and open your dataset in WPS Spreadsheet.
2. Select the target cell: Click on the cell where you want your unique subtotal to be displayed.
3. Input the array formula: Paste your chosen SUBTOTAL and COUNTIF formula into the formula bar.
4. Calculate instantly: Press Enter to instantly calculate the unique values, which will dynamically update when you apply Data Filters.

100% compatible with Microsoft Excel formulas (.xlsx format)High-performance data processing for large and complex datasetsBuilt-in dynamic array formula support without needing Ctrl+Shift+EnterLightweight, free to download, and easy to use

microsoft office alternative - wps office

Frequently Asked Questions

Why does my SUBTOTAL array formula return a #VALUE! error?

This typically happens if the ranges in your OFFSET and COUNTIF functions do not perfectly match in size (e.g., one range is 100 rows and the other is 101 rows), or if the formula is not properly entered as an array formula using Ctrl+Shift+Enter in older spreadsheet versions.

Does the helper column method automatically update when I apply a filter?

A standard SUMIFS based on the provided helper column will sum all unique values regardless of the active filter. To make the helper column respect filters, you must incorporate a SUBTOTAL function (e.g., checking if the row is visible using SUBTOTAL(103, ...)) inside the helper column's IF statement.

Can I use UNIQUE and FILTER functions instead of these complex formulas?

Yes, if you are using a modern spreadsheet application that supports dynamic arrays (like the latest versions of WPS Office or Microsoft 365), you can use a combination of SUM, UNIQUE, and FILTER functions for a much simpler syntax compared to traditional array formulas.

Excel & Data Problems