To calculate a subtotal in Excel, add the amounts in the relevant range; with item amounts in B2:B6, enter =SUM(B2:B6) in the cell where you want the subtotal. If those five cells contain 12, 8, 15, 5, and 10, the subtotal is 50. By hand, add the amounts the same way. A subtotal is the sum before any additional charges, such as tax, are added, or before any final total is calculated.
What You’ll Need
- A list of the amounts or items you want to total.
- For a spreadsheet, Microsoft Excel for Microsoft 365 on Windows and a blank cell for the result.
- If your list has quantities and unit prices, both values for each item. If you plan to calculate tax or a discount, the applicable rate or amount.
Step-by-Step
- Decide what the subtotal includes. Identify the items that belong in the calculation. On a receipt, the subtotal usually means the combined price of the items before tax. In a quote, order, or worksheet, it may mean a group total before shipping, fees, discounts, or other adjustments. Write down whether you are totaling amounts already calculated or need to calculate each line from its quantity and price. This definition matters: including tax or a fee too early changes what the subtotal represents.
- Collect and check the amounts. Make a list of the values to add. For a hand calculation, align decimal places so dollars and cents sit in matching columns. Add cents first, then dollars, and carry any complete dollar amount from the cents column. For an Excel calculation, enter numeric values in one column rather than typing a currency symbol as part of the value. You can apply currency formatting afterward; a number formatted as currency remains usable in calculations.
- Calculate each line amount if needed. If every row already has a line amount, skip this step. Otherwise, multiply the quantity by the unit price for each item. For example, with item names in A2:A6, quantities in B2:B6, unit prices in C2:C6, and line amounts to be placed in D2:D6, enter
=B2*C2in D2. If B2 is 2 and C2 is 12, the line amount is 24. Copy the formula down through D6 using the fill handle at the lower-right corner of the selected cell. Check that each row’s quantity and price line up; a shifted reference can produce a plausible but incorrect amount. - Add the amounts by hand or with SUM. For a manual subtotal, add each line amount once and check the result by adding in a different order or using a calculator. For Excel, with the completed line amounts in D2:D6, enter
=SUM(D2:D6)in D7. If the line amounts are 24, 8, 15, 10, and 8, the subtotal in D7 is 65. In Excel for Microsoft 365 on Windows, select D7, type the formula, and press Enter. You can also select D7 and choose Home > AutoSum, check that Excel highlights D2:D6, and press Enter. On Mac, the formula and AutoSum workflow are similar; keyboard shortcuts may differ from Windows. - Confirm the included rows. Check the first and last cell in the range and make sure every intended item is included exactly once. If you are adding values from separate areas rather than one continuous column, you can specify more than one range. With amounts in B2:B4 and B7:B8, enter
=SUM(B2:B4,B7:B8)in the result cell. Confirm that the blank rows between the groups are intentionally excluded. - Add any later charges separately. Keep the subtotal distinct from tax, shipping, or other adjustments so it is clear how the final total was reached. For an illustrative example only, if the subtotal of 65 is in D7 and you are applying an 8% rate, enter
=D7*8%in D8; the calculated amount is 5.20. The 8% rate is only an example, not a recommendation about what rate applies. Check the rules and rate relevant to your situation with the appropriate tax authority or a qualified professional. With the subtotal in D7 and the calculated charge in D8, enter=D7+D8in D9; the total is 70.20. If you have a discount, decide whether it should be applied before or after tax under the rules that apply to the transaction. - Format and label the result. Label the result cell Subtotal so it is not mistaken for the final total. In Excel, select the cells and choose Home > Number to apply a suitable currency or number format. Formatting changes how a value appears, not the underlying calculation. If the result displays too many decimal places, adjust the displayed decimal places without rounding the source amounts unless you intend to change the calculation.
For a hand calculation, use the same sequence: determine which items belong, calculate any quantity-times-price line amounts, add those amounts, then keep later charges separate. A written breakdown makes it easier to verify the result and explain how you reached it.
Common Mistakes
- Adding the wrong values. Do not add unit prices alone when the order contains multiple units. First calculate each quantity-times-price line amount, then add the line amounts.
- Including a charge twice. If the amounts in the list already include tax, adding tax again will overstate the final total. Check whether listed prices are tax-inclusive or pre-tax before proceeding.
- Leaving out a row or including an unrelated one. A SUM range that starts or ends one row too early can silently omit an item. A range that reaches too far may include a previous total or a different group of values.
- Entering amounts as text. A value copied with an extra character, such as an apostrophe, may not be treated as a number. Excel may ignore text in a SUM range, so the result can be lower than expected.
- Rounding every line too soon. Rounding each intermediate amount can create a small difference from a calculation that keeps full precision and rounds only the displayed or final result. Follow the rounding rules that apply to your use case.
- Confusing a subtotal with a final total. A subtotal normally does not include every later adjustment. Label subtotal, tax, shipping, discounts, and final total clearly rather than presenting one number without context.
Tips
- Use one row per item and clear column headings such as Item, Quantity, Unit Price, and Line Amount. This structure makes errors easier to spot.
- Use Excel’s SUM function rather than manually typing a chain of additions. A range-based formula is easier to update when you edit individual amounts.
- Keep the subtotal in its own cell and base later calculations on that cell. This avoids repeating the same addition in several places and makes the worksheet easier to audit.
- For a quick hand check, group amounts into convenient pairs or tens, then compare the result with the spreadsheet. With a long list, check a few line amounts individually as well as the final sum.
- If you add more rows later, confirm that the subtotal range includes them. Consider placing the data in an Excel table using Insert > Table; table formulas can expand as rows are added, but inspect the resulting total and range rather than assuming a change was included.
Troubleshooting
The subtotal is zero or smaller than expected. Confirm that the cells contain numbers, not text, and inspect the first and last cells in the formula’s range. If values were imported, look for leading apostrophes, spaces, or symbols typed directly into cells. Converting text values to numbers can resolve the issue; after conversion, recalculate and compare the result with a manual check.
The formula appears as text instead of calculating. The cell may be formatted as Text, or the formula may have been entered with an apostrophe before the equals sign. Change the cell format to General, re-enter the formula, and press Enter. Also check that Excel’s Show Formulas setting has not been turned on; when it is enabled, Excel displays formulas in cells rather than their results.
The answer is higher than the expected amount. Check for duplicate item rows, a range that includes an existing total, and tax or fees already included in the source values. If you have both unit price and line amount columns, sum the line amount column rather than adding both columns together.
Filtered or hidden rows affect the result. SUM includes values in hidden rows. If you want a subtotal that responds to filtered rows, use Excel’s SUBTOTAL function. With amounts in D2:D6 and the result in D7, enter =SUBTOTAL(9,D2:D6) in D7; it sums the visible values and ignores rows excluded by a filter, but includes manually hidden rows. To exclude manually hidden rows as well, with the same layout enter =SUBTOTAL(109,D2:D6). The result depends on which rows are visible, so clear filters or review hidden rows if the number changes unexpectedly. SUBTOTAL also ignores other SUBTOTAL results within its reference range, helping avoid double-counting nested subtotals.
The displayed amount differs by a cent. Check whether the source cells contain more decimal places than are displayed. Increase the displayed decimal places temporarily to inspect the values, and check whether your process rounds each line or only the final amount. Use the method required for your records or applicable rules.
Frequently Asked Questions
What is a subtotal?
A subtotal is the sum of a selected set of amounts before any later charges or adjustments included in the final total. The exact items included depend on the worksheet, receipt, or calculation.
How do I calculate a subtotal by hand?
List the amounts that belong, calculate any line amounts that are missing, and add each amount once. Align decimal places, carry cents into dollars where needed, and check the sum with a second addition or calculator.
What is the Excel formula for a subtotal?
For a simple sum, use SUM with the cells you want to add. For example, with amounts in B2:B6 and the answer in B7, enter =SUM(B2:B6) in B7. Use SUBTOTAL instead when you need the result to respond to filtered rows.
Does a subtotal include tax?
Usually, a subtotal is shown before tax, but terminology and pricing practices can vary. Check whether the listed item prices already include tax and label the figures clearly. For tax treatment or the correct rate, consult the relevant authority or a qualified professional.
How is subtotal different from total?
A subtotal is an intermediate sum for selected items or a group. A final total is the amount after the additions or deductions that apply, such as tax, shipping, or discounts, have been accounted for.
Can I calculate a subtotal for only visible rows?
Yes. In Excel, SUBTOTAL can exclude rows hidden by a filter, and its function number determines whether manually hidden rows are included. Use the troubleshooting examples above and choose the behavior that matches your worksheet.
Related
- CSV vs Excel: Key Differences and When to Use Each
- Delivery Schedule Excel: Create and Manage a Delivery Plan
- Default Borders in Excel: What They Are and How to Set Them
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Dividing Two Cells: ExamplesTo divide two cells in Excel, use a forward slash in a formula: with the dividend in A1…
- Excel TEXTJOIN Limit: Character Cap and WorkaroundsIn Excel, TEXTJOIN can return a maximum of 32,767 characters in one cell—the worksheet cell-content limit. If its…
- JavaScript Functions: How They Work and When to Use ThemIn JavaScript, a function is a reusable block of code that can receive values, perform work, and optionally…
- Arduino Pass String to Function: Examples, Types, and SafetyYes. In Arduino C++, pass a String to a function by putting its type in the parameter list,…
- COUNTIF in Excel: How It Works and How to Use ItExcel’s COUNTIF function counts cells in a range that meet one condition. Give it a range to inspect…
- Copy Formula in Excel Without Dragging: 6 WaysTo copy a formula in Excel without dragging, copy and paste it, use Ctrl+D to fill down, or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.