How to sum values without or exclude subtotals in Excel?
When working with datasets in Excel, it's common to organize and analyze your numbers by calculating subtotals for different groups or categories. However, after you’ve added these subtotals, you may want to calculate a grand total that excludes these subtotal values, preventing any double-counting in your final sum. The challenge is that, when directly adding up all the numbers, subtotals are included in the overall total, resulting in a total sum that is larger than intended. To avoid this, you need to find a way to sum the original data while excluding the subtotal rows. In this article, we present several practical methods to solve this problem, so you can efficiently calculate accurate results in different scenarios.
Sum values without subtotals with Sum function in Excel
A widely adopted approach for calculating group subtotals is to insert the SUM function within or below each relevant group. However, when you wish to compute the grand total for the entire list while ignoring these group subtotal rows, an extra step is often required. Here's a method you can use to achieve this:
1. Use the SUM function within each group to generate the respective subtotals, as shown in this screenshot:
2. Next, to find the grand total without including those subtotal rows, enter the following formula into a blank cell where you'd like the result to appear:
=SUM(B2:B21)/2
Press Enter to obtain the correct total. This formula works because it assumes that you have inserted subtotals with the SUM function immediately following relevant groups, effectively doubling the sum of the raw data. Dividing by 2 removes the duplicated total. Do note, this method is most suitable when the dataset is structured such that the sum of the original values and subtotals together forms exactly twice the sum of the original data.
If your data structure is more complex, or the number of subtotal rows varies, alternative methods described below may offer a more accurate and flexible solution.
Sum value without subtotals with Subtotal function in Excel
The SUBTOTAL function in Excel provides a built-in way to perform calculations on filtered or visible rows only. This makes it especially useful when working with lists containing subtotals, as SUBTOTAL formulas can be configured to ignore other SUBTOTAL results within the range, thus preventing any double-counting of values.
To use the SUBTOTAL function in this context, follow these steps:
1. Enter the SUBTOTAL function for each group as follows:
=SUBTOTAL(9,B2:B10)
This applies the SUM operation (function number 9) to the specified range and is often used as part of Excel’s built-in Data > Subtotal feature.
2. For the grand total that excludes internal subtotals, enter the following formula in your chosen cell:
=SUBTOTAL(9,B2:B21)
Press Enter to automatically sum the data values, ignoring any nested SUBTOTAL functions within the range. This approach is suitable for lists that use the SUBTOTAL function for group summaries, and it eliminates the risk of summing subtotals multiple times.
Be aware that the SUBTOTAL function works best when subtotal cells were generated using SUBTOTAL rather than SUM. Also, if you filter or hide rows, SUBTOTAL can be set to sum only visible (unhidden) data, which is helpful in dynamic reporting situations.
VBA Code - Sum values excluding subtotal rows
If your data structure is complex or you want an automated method to sum only the raw data rows (excluding subtotals), you can utilize a simple VBA macro. This approach allows you to programmatically identify and sum only those rows that match your criteria, such as based on specific formatting, the presence of formulas, or other distinguishing attributes.
For example, if your subtotal rows are calculated with formulas (such as SUM or SUBTOTAL), and your raw data rows contain only static values, you can set the VBA code to sum only those cells in a specific column that do not contain formulas. Here’s how you can do it:
1. Click Developer Tools > Visual Basic to open the Microsoft Visual Basic for Applications window.
2. In the new window, click Insert > Module and paste the following code into the module:
Sub SumNonSubtotalRows()
Dim WorkRng As Range
Dim SumResult As Double
Dim cell As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the range to sum (e.g., B2:B21)", xTitleId, WorkRng.Address, Type:=8)
SumResult = 0
For Each cell In WorkRng
If Not cell.HasFormula Then
SumResult = SumResult + cell.Value
End If
Next
MsgBox "The sum of non-subtotal rows is: " & SumResult, vbInformation, xTitleId
End Sub3. Click the 
Run button to execute the code. A prompt will appear for you to select the range containing your data. The macro will then sum only the cells in your selection that do not contain formulas, effectively skipping typical subtotal rows.
Filtering - Use filter and SUBTOTAL to sum only visible (non-subtotal) rows
If your dataset allows you to filter out subtotal rows (for example, there is a label, keyword, or pattern to identify them), you can use Excel’s built-in Filter feature together with the SUBTOTAL function to add only the visible (non-filtered) data rows. This technique is practical for cases where subtotals have a consistent indicator or label.
1. Click anywhere in your dataset, then go to the Data tab and click Filter to enable filters for your columns.
2. Click the drop-down in your helper column, uncheck "Subtotal" to hide subtotal rows, and display only the original data.
3. In a separate cell, enter the following formula to sum only visible (filtered) rows.
=SUBTOTAL(9,B2:B21)The SUBTOTAL function (with function number 9 for SUM) will ignore any hidden rows, returning the total of only those rows currently visible (that is, the non-subtotal rows you kept after filtering). This solution is quick and dynamic—if you reapply filters or expand your dataset, the formula will update accordingly.
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