How to sum visible cells only based on criteria in Excel?
In Excel, users can typically sum cells based on specific criteria using the SUMIFS function. However, when dealing with filtered data, simply applying SUMIFS will include both visible and hidden cells in the calculation. This often leads to incorrect results if you need to sum only the visible (i.e., unfiltered) cells that match particular criteria, as shown in the screenshot below.
Sum visible cells only based on one or more criteria with a helper column
Sum visible cells only based on one or more criteria with a formula
It's a common need in daily reporting and data analysis workflows to accurately aggregate data in filtered tables, such as when calculating sales amounts for a particular product or category after applying some filters. Doing this incorrectly may result in totals that include data you did not intend, so it's important to use techniques that sum only the visible data you see on your screen.
This article introduces several practical methods suitable for different scenarios and proficiency levels, each with its advantages and possible limitations. You can select a solution that best fits your worksheet size, data structure, and operational habits. Detailed steps for each solution are provided below, along with explanations of potential errors and ways to optimize the calculation process for more reliable outcomes.
Sum visible cells only based on one or more criteria with a helper column
One of the most intuitive and stable approaches to summing visible cells based on specific criteria is to use a helper column that returns values only for visible rows, and then leverage the SUMIFS function with your desired conditions. This is especially effective if your dataset is frequently filtered in various ways or if you need to set up calculations that colleagues can easily understand or modify.
Advantages: Simple to set up; all logic and calculations remain visible in the worksheet; best for small to medium tables; robust when needing to adjust or audit formulas.
Limitations: Creates additional columns; may need formula updates if row layout changes; extensive use might become cumbersome on very large datasets.
For example, to only sum the values of orders for the product "Hoodie" in a filtered range:
1. Enter or copy the following formula into a blank column next to your dataset (for example, into cell E2, assuming D is your value column):
Drag the fill handle down to fill this formula through all rows in your data range. This formula will return the value from column D if the row is visible and 0 if the row is hidden by filtering.
2. After generating the helper values in column E, use a SUMIFS function to sum only the visible values based on your criteria. For example, to sum for "Hoodie" in column A:
You may add more criteria by extending the SUMIFS arguments in the format of =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], [criteria_range3, criteria3], ...). Always check your ranges to ensure correct alignment and expected results.
Be aware: If you rearrange, insert, or delete rows after setting up your formulas, double-check to ensure all references still match your data structure. Sometimes errors can result from misaligned ranges or forgetting to update your criteria cells.
Sum visible cells only based on criteria with a formula
If you prefer a formula-based solution that does not require adding helper columns, you can use a combination of SUMPRODUCT, SUBTOTAL, OFFSET, ROW, and MIN functions to sum visible cells according to specific criteria. This approach is best for experienced Excel users comfortable with array formulas, and is particularly useful when you wish to keep your sheet tidy without extra columns.
Advantages: No need for extra worksheet columns; flexible and dynamic; formula updates instantly as you filter or change criteria.
Limitations: Formulas can be complex to read or debug, especially for those not familiar with array functions; performance may slow in very large tables.
Copy or enter the following formula in a blank cell (for example, to sum visible cells for "Hoodie" in A2:A12, with actual values in D2:D12, and the criteria in A17):
After entering the formula, press Enter to get the desired result, as shown below:
Pay attention: This approach is sensitive to the ranges specified—mismatched or overlapped ranges may trigger errors or unexpected results. Test edge cases, especially when filtering changes the number or position of visible rows.
Sum visible cells only based on criteria using VBA code
For advanced users, using VBA offers a flexible way to sum only visible cells per specific criteria, especially when handling complex scenarios or large datasets where standard formulas may suffer from performance bottlenecks or where criteria counting includes multi-condition logic that's difficult to express in a single formula. VBA can iterate through each visible row, test for your conditions, and compute the sum efficiently. This is particularly suitable for repeated reporting tasks or when automating summary calculations.
Advantages: Can easily handle large datasets, multiple or dynamic criteria and complex logic; process executes quickly even with thousands of rows; reduces risk of errors from manual formula changes.
Limitations: Requires enabling macros; some users may not be familiar with VBA or have adequate permissions; changes require access to Macro Editor. Always make a backup before running VBA on important datasets.
1. To get started, open the VBA Editor by clicking Developer Tools > Visual Basic. In the window that appears, go to Insert > Module, and paste the following code into the new module:
Sub SumVisibleByCriteria()
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range
Dim criteriaColumn As Range
Dim sumColumn As Range
Dim criteriaValue As Variant
Dim total As Double
Dim lastRow As Long
Dim criteriaColNum As Integer
Dim sumColNum As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = Application.ActiveSheet
' Prompt user for criteria column and sum column
Set criteriaColumn = Application.InputBox("Select the criteria range (e.g., A2:A100):", xTitleId, Type:=8)
Set sumColumn = Application.InputBox("Select the values range to sum (e.g., D2:D100):", xTitleId, Type:=8)
criteriaValue = Application.InputBox("Enter the criteria value to match:", xTitleId, Type:=2)
If criteriaColumn Is Nothing Or sumColumn Is Nothing Or criteriaValue = "" Then
MsgBox "Operation cancelled.", vbInformation, xTitleId
Exit Sub
End If
If criteriaColumn.Rows.Count <> sumColumn.Rows.Count Then
MsgBox "Criteria and sum ranges must be the same number of rows.", vbCritical, xTitleId
Exit Sub
End If
total = 0
For Each cell In criteriaColumn
If Not cell.EntireRow.Hidden Then
If cell.Value = criteriaValue Then
total = total + sumColumn.Cells(cell.Row - criteriaColumn.Cells(1).Row + 1).Value
End If
End If
Next cell
MsgBox "The sum of visible cells matching the criteria is: " & total, vbInformation, xTitleId
End Sub2. Click the 
"Run" button (or press F5) to execute the code. A dialog will prompt you to select both the criteria range (such as your product names), the value range to sum, and what value you want as the filter (e.g., "Hoodie"). The macro will sum only those visible rows where your criteria is met and show the result in a pop-up message.
Practical tips: Use this VBA code when you often need to recalculate your sums after changing your data or filters. You can further expand the VBA code to work for multiple criteria by adding more input prompts or logical conditions.
Troubleshooting: Always ensure the ranges you select for criteria and values have the same number of rows and belong to the same columns as your filtered data. If the code reports an error or doesn't return your expected sum, double-check your filter settings and active selection.
Summary suggestions: For data analysis requiring repeated visible-only calculation, saving this macro in your Personal Macro Workbook can speed up your day-to-day reporting. If a dialog does not appear, verify your macro settings and security permissions.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in