How do I sum values excluding any subtotals! (Aggregate and Subtotal)

We can do this in excel with two formulas:

  1. SUBTOTAL: Sums the values in a given range
  2. AGGREGATE: Performs a set action with a given range

Syntax

=SUBTOTAL(chosen_action, given_range)
=AGGREGATE(chosen_action, what_values_to_ignore, given_range)

Example

Let’s use an example to illustrate the formula. We want to create a subtotal for each block and an overall total for the column (the black cells).

The Steps – Aggregate and Subtotal

Step One: SUBTOTAL

First we create subtotals for our blocks.

chosen_action: We will choose to sum our values, function number 9 (function_num)

=SUBTOTAL(sum our values

given_range: Our block we want to sum

=SUBTOTAL(sum our values, the block to sum)

Subtotal Result

formula

=SUBTOTAL(9,C3:C5)
=SUBTOTAL(9,C8:C9)

output

Step Two: AGGREGATE

chosen_action: We will choose to sum our values, function number 9 (function_num) 

=SUBTOTAL(sum our values

what_values_to_ignore: We will choose to ignore any nested subtotals, option number 9(options)

=SUBTOTAL(sum our values, ignore nested subtotals

given_range: Our range we want to sum (array)

=SUBTOTAL(sum our values, ignore nested subtotals, the range to sum)

Result

formula

=AGGREGATE(9,0,C3:C10)

output

And there you have it, totals and subtotal independent of each other!