Function Problems

How to Count Filtered Coins and Sum Their Values in Excel

Adam Davis Oct 9, 2026 870 views

Question details

The user needs a way to count specific items (like coin types) and sum their corresponding monetary values strictly for visible, filtered rows in a spreadsheet.

How to Count Filtered Coins and Sum Their Values in Excel

Product: Excel
Device & OS: not provided
Scenario: Working with a dataset containing items and values in separate columns, and applying filters to isolate specific categories while calculating dynamic totals.
Observed behavior: Standard functions like COUNT and SUM calculate values for all rows in a range, including hidden ones, whereas the goal is to aggregate data for visible rows only.

Before you start

Ensure your coin types and their corresponding values are organized into clear, separate columns without any entirely blank rows breaking the dataset.

Solution 1Recommended

Use the SUBTOTAL Function for Filtered Data

The SUBTOTAL function is designed specifically to perform calculations on visible rows only, making it the ideal tool for counting and summing filtered data dynamically.

Unlike standard COUNT and SUM functions, SUBTOTAL automatically ignores rows hidden by a filter. By using specific function numbers (such as 103 for COUNTA and 109 for SUM), you can dynamically calculate totals for whatever data is currently visible on your screen.

Format Data as a Table

Select your entire dataset (including column headers) and press Ctrl+T to convert it into an Excel Table. This automatically adds filter dropdowns to your header row.

Apply a Filter

Click the filter dropdown arrow on your 'Coin Type' column header, uncheck 'Select All', and select the specific coin type you want to view (e.g., Dimes).

Count the Visible Coins

In an empty cell outside your table, type '=SUBTOTAL(103, A2:A100)' (assuming your coin types are located in A2:A100) and press Enter. This counts the visible text entries.

Sum the Filtered Values

In another empty cell, type '=SUBTOTAL(109, B2:B100)' (assuming the coin values are in B2:B100) and press Enter to calculate the total monetary value of the visible rows.

Use the SUBTOTAL Function for Filtered Data

Understanding SUBTOTAL Arguments: The first argument determines the mathematical operation: '103' tells Excel to perform a COUNTA operation while ignoring hidden rows, and '109' tells Excel to perform a SUM operation ignoring hidden rows.

Easily Manage Spreadsheets

Count and Sum Filtered Data Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced formulas like SUBTOTAL and offers an intuitive interface for filtering tables. You can seamlessly calculate and manage dynamic datasets with full formula compatibility.

1. Open Your Data: Launch WPS Spreadsheet and open the workbook containing your coin types and values.
2. Apply Filters: Highlight your data, navigate to the 'Data' tab on the top ribbon, and click the 'AutoFilter' button to enable dropdown menus on your headers.
3. Enter the SUBTOTAL Formula: Use the formula '=SUBTOTAL(103, Range)' to count visible categories and '=SUBTOTAL(109, Range)' to sum their values. The results will update dynamically as you change your filters.

Fully compatible with Microsoft Excel (.xlsx) files and advanced formulas.Intuitive data filtering and dynamic table creation capabilities.Built-in formula suggestions to easily insert and configure functions like SUBTOTAL.Lightweight, free-to-use software that runs smoothly on Windows, Mac, and Linux devices.

Frequently Asked Questions

Why do standard COUNT and SUM functions include hidden rows?

Standard aggregation functions like SUM and COUNT are designed to evaluate the entire specified cell range, regardless of visibility. To evaluate only visible rows, you must use functions specifically designed for dynamic calculation, such as SUBTOTAL or AGGREGATE.

What is the difference between SUBTOTAL 9 and 109?

Both '9' and '109' perform a sum operation and will ignore rows hidden by an AutoFilter. However, '109' will also ignore rows that have been manually hidden (e.g., right-clicking a row and selecting Hide), whereas '9' still includes those manually hidden rows.

Can I use the AGGREGATE function instead of SUBTOTAL?

Yes, the AGGREGATE function offers similar functionality but provides more options, such as the ability to ignore error values within the filtered range. For example, '=AGGREGATE(9, 5, B2:B100)' will sum the range while ignoring hidden rows.

How do I filter for multiple item types at once?

Click the filter arrow in your column header, ensure 'Select All' is unchecked, and then check the boxes next to all the categories you want to view. Click OK, and your SUBTOTAL formulas will automatically update to reflect the newly visible rows.

Excel & Data Problems