How to count / sum cells based on a filter with criteria in Excel?
In day-to-day data analysis using Excel, it's common to need totals or counts that consider only filtered rows, especially when working with long lists or reports where you need to focus on specific data segments. The basic Excel functions like COUNTA and SUM are straightforward for unfiltered ranges, but when you apply filters—such as hiding certain rows or narrowing the view based on criteria—these standard functions can include hidden rows in their results, leading to inaccurate counts or sums. To reliably calculate totals and counts that respect your filters, including conditions or criteria, this tutorial offers a range of practical solutions suited to different scenarios and skill levels.
Count / Sum cells based on a filter with formulas
Count / Sum cells based on a filter with Kutools for Excel
Count / Sum cells based on a filter with certain criteria by using formulas
Count / Sum cells based on a filter with formulas
Excel provides the SUBTOTAL function for filtered data, allowing you to accurately count or sum visible cells while disregarding hidden ones. This is especially valuable when working with datasets that have been filtered for specific values or conditions. Using SUBTOTAL ensures the calculations adapt automatically as filter criteria change, maintaining precision in your analysis.
To count the cells in a filtered range, use the following formula in a cell where you want the result to appear (for example, D1):
=SUBTOTAL(3, C6:C19)Here, C6:C19 is the filtered data range you want to count. After entering the formula, press Enter, and it will return the count of only the visible (filtered) cells in this range.
To sum values in a filtered range, enter the following formula (for example, in D2):
=SUBTOTAL(9, C6:C19)This sums only the visible cells after filtering. Press Enter to see the total.
Tips: The first number in the SUBTOTAL function is a function_num parameter that determines the calculation type.3 stands for COUNTA (count non-blank values), and 9 stands for SUM. Always check that your filter is active and correct before relying on these results. If the data range changes, adjust the cell references accordingly. The formula recalculates as you change or clear the filter.
Count / Sum cells based on a filter with Kutools for Excel
With Kutools for Excel, users can leverage specialized functions—COUNTVISIBLE and SUMVISIBLE—to immediately display count and sum results based only on visible (i.e., filtered and non-hidden) cells, bypassing the limitations of standard Excel formulas. This is especially convenient for frequent data filter analysis, saving time and reducing manual errors.
After installing Kutools for Excel, enter the following formulas in your worksheet to calculate results from filtered cells (e.g., in D1 or D2):
To count filtered cells, use:
=COUNTVISIBLE(C6:C19)To sum the visible, filtered cells, use:
=SUMVISIBLE(C6:C19)
Tips: These functions work with both manually hidden and filtered rows, ensuring your calculations reflect exactly what’s displayed. You can also access these features via the Kutools menu: click Kutools > Kutools Functions > Statistical & Math > AVERAGEVISIBLE / COUNTVISIBLE / SUMVISIBLE. This provides one-click access to useful summary functions for filtered data sets.
Notes: If you change the filter or hide rows, Kutools functions will update automatically. These formulas are available only after installing Kutools, and not in standard Excel installations.
Kutools for Excel - Supercharge Excel with over 300 essential tools, making your work faster and easier, and take advantage of AI features for smarter data processing and productivity. Get It Now
Count / Sum cells based on a filter with certain criteria by using formulas
In practice, you may need to count or sum filtered data based on an additional condition—for example, tallying only the rows where a certain name appears. While filtering can visually narrow down your data, applying formulas allows you to perform these calculations on the fly without constantly adjusting your filters. Below, find useful formulas for handling such scenarios.
Count cells based on filter data with certain criteria:
To count visible (filtered) cells that match a specific condition—such as those with the name "Nelly"—enter the following formula in a cell (e.g., D1):
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B6:B19,ROW(B6:B19)-MIN(ROW(B6:B19)),,1)), --(B6:B19="Nelly"))Here, B6:B19 represents the data range, and "Nelly" is your criteria. This formula will only count the visible rows meeting the specified condition after filtering. Press Enter, and the cell displays the count.
Sum cells based on filter data with certain criteria:
If you also need to sum items—with the same criteria—use this advanced formula (for example, enter in D2):
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B6:B19,ROW(B6:B19)-MIN(ROW(B6:B19)),,1)),(B6:B19="Nelly")*(C6:C19))In this formula, B6:B19 is the criteria column, C6:C19 is the amount column, and "Nelly" remains the criteria. The formula gives the sum of corresponding values in C6:C19 where the condition is met and rows are visible. Hit Enter to confirm and show the sum.
Tips: When entering these formulas, make sure your data ranges and criteria match your filtered data. The formulas dynamically respect changes to filters, displaying updated totals or counts. For other criteria, simply replace "Nelly" as needed.
VBA Code - Automatically count or sum only visible cells based on filter and criteria via a custom macro
For users comfortable with macros, VBA offers a flexible way to count or sum only visible cells while optionally including criteria—very useful if your filters change frequently or you need to automate these calculations. Unlike formulas, macros can quickly process large datasets, and you can customize behavior to fit specific needs.
Applicable scenarios: Recommended for those who work with large filtered tables and need custom calculations not directly supported by standard formulas. Pros include automation and versatility, while cons might be the initial setup required and the need for macro enabling.
Precautions: Always save your work before running VBA scripts. Macros are only available on desktop Excel editions, not on web or mobile versions.
1. Click Developer Tools > Visual Basic. In the opened Microsoft Visual Basic for Applications window, click Insert > Module and paste the following code into the module panel:
Sub SumOrCountVisibleCellsWithCriteria()
Dim CriteriaCol As Range
Dim DataCol As Range
Dim Criteria As String
Dim Total As Double
Dim Count As Long
Dim i As Integer
Dim LastRow As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set CriteriaCol = Application.InputBox("Select criteria column (e.g. B6:B19)", xTitleId, Type:=8)
Set DataCol = Application.InputBox("Select data/sum column (for sum, e.g. C6:C19; same as criteria for count)", xTitleId, Type:=8)
Criteria = Application.InputBox("Enter criteria (e.g. Nelly)", xTitleId, "", Type:=2)
Total = 0
Count = 0
LastRow = CriteriaCol.Rows.Count
For i = 1 To LastRow
If Not CriteriaCol.Rows(i).EntireRow.Hidden Then
If CriteriaCol.Cells(i, 1).Value = Criteria Then
Total = Total + DataCol.Cells(i, 1).Value
Count = Count + 1
End If
End If
Next i
MsgBox "Sum: " & Total & vbCrLf & "Count: " & Count, vbInformation, xTitleId
End Sub2. Click the 
button to execute the macro. A dialog box appears prompting you to select the criteria column and the sum/count column, then specify your desired criteria (such as a name). Upon completion, the macro displays both the sum and count for visible cells matching your criteria.
Tips: This macro combines both counting and summing within visible, filtered data. Change CriteriaCol and DataCol selections for different analysis needs. Always make sure the columns selected match your filter setup. If you only want to count (not sum), select the same range for both inputs.
Troubleshooting: If you receive a runtime error, ensure that you select ranges of equal size and that your criteria exactly match the text in the cells. For large datasets, performance may vary; consider filtering to essential rows before running the macro.
Pivot Table - Use pivot tables to summarize (count/sum) filtered data, including by criteria, with interactive filtering options
Pivot tables are a versatile and interactive tool in Excel for summarizing large amounts of data, including filtered results. They allow easy grouping, counting, and summing by criteria (such as name or category), and built-in filters let you quickly change which data is shown.
Applicable scenarios: Ideal for situations where you need dynamic summaries, flexible aggregation by different fields, or wish to interactively explore results by changing criteria. Pros include user-friendly operation and instant recalculation with drag-and-drop functionality.
Instructions for use:
1. Select your filtered data range, ensuring it includes all columns you'll analyze (including headers).
2. Go to Insert > Pivot Table. In the dialog box, make sure the table/range is correct and choose where to place the pivot table (new or existing worksheet).
3. In the pivot table field list, drag the criteria field (e.g., "Name") to the Rows area. Drag the target field (e.g., "Order Amount") to the Values area. By default, this summarizes the data with a sum. You can click it and change to count or other summaries as needed.
4. Use the built-in filter drop-downs in the pivot table to show only particular items (such as filtering for "Nelly"), or apply multiple criteria to focus on relevant data.
5. Your pivot table will instantly update to show the sum and/or count for visible criteria. You can rearrange fields, add more filters, or format the pivot table for better readability.
Tips: Pivot tables do not directly respond to worksheet filters but have their own filter controls, which can be much more powerful and flexible. For advanced analysis, use slicers or additional calculated fields. Refresh your pivot table if you update the source data.
Troubleshooting: If results do not match expectations, verify your field selections and make sure the source range includes all relevant data. If your data is not structured with clear headers, insert them before creating the pivot table.
Summary Suggestions: Each solution in this tutorial addresses a specific need—from quick formulas to automation and interactive analysis. Choose SUBTOTAL or Kutools functions for straightforward totals and counts on filtered data, advanced formulas for criteria-driven results, macro solutions for automation, or pivot tables for highly flexible summaries and exploration. Always double-check cell references and criteria to avoid mismatched results. For further efficiency, keep your data organized with clear headers and consistent formatting.
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