How to calculate conditional subtotals in an Excel revenue sheet

When managing revenue data in Excel, you often need more than just totals—you need conditional subtotals. For example, what if you want to see revenue totals by product, region, or sales rep? Excel makes it easy with a few built-in tools. Here’s how to calculate conditional subtotals that adapt to your data.

Method 1: Use the SUBTOTAL Function with Filters

If you just want to subtotal visible (filtered) rows, the SUBTOTAL function is your go-to.

How to do it:

1. Filter your data using the filter buttons (Data → Filter).
2. Use the formula: =SUBTOTAL(9, B2:B100)
   • 9 tells Excel to perform a SUM.
   • B2:B100 is your revenue column.

This adds only the rows currently visible—perfect when filtering by region or product.

Method 2: Use SUMIF for One Condition

To subtotal based on one condition (like total revenue for a single product), use SUMIF.

Example:

You have a list of product names in column A and revenue in column B.

To get the total revenue for “Product A”:

=SUMIF(A2:A100, "Product A", B2:B100)

Or, refer to a cell:

=SUMIF(A2:A100, D1, B2:B100)

Where D1 contains “Product A”.

Method 3: Use SUMIFS for Multiple Conditions

Need subtotals for more than one condition? Use SUMIFS.

Example:

Get the total revenue for “Product A” sold in “Region 1”:

=SUMIFS(B2:B100, A2:A100, "Product A", C2:C100, "Region 1")

• B2:B100 = revenue
• A2:A100 = product
• C2:C100 = region

You can add more conditions if needed—just follow the pattern.

Method 4: Use Pivot Tables for Dynamic Subtotals

For larger datasets or when you want interactive subtotals, use a Pivot Table.

Steps:

1. Select your data.
2. Go to Insert → PivotTable.
3. In the PivotTable field list:
   • Drag Product or Region into Rows.
   • Drag Revenue into Values.

You’ll instantly get subtotals by product, region, or anything else you choose.

Final Tips

• Use named ranges to keep formulas readable.
• Combine Excel Tables with formulas for dynamic range referencing.
• Use Pivot Tables when working with large or changing datasets.

Bottom line: Whether you’re summing filtered data, creating quick subtotals by condition, or analyzing across multiple dimensions, Excel has the tools to make your revenue sheets more insightful and useful.

Want a downloadable example file? Drop a comment below and I’ll send one your way.