How do I sum values excluding any subtotals! (Aggregate and Subtotal)
We can do this in excel with two formulas:
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).
![](https://www.helpxel.com/wp-content/uploads/2021/10/aggsub_goal.jpg)
The Steps – Aggregate and Subtotal
![](https://www.helpxel.com/wp-content/uploads/2021/10/AGG-SUB-Example.jpg)
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
![](https://www.helpxel.com/wp-content/uploads/2021/10/Sub_func.jpg)
given_range: Our block we want to sum
=SUBTOTAL(sum our values, the block to sum)
![](https://www.helpxel.com/wp-content/uploads/2021/10/Sub_array.jpg)
Subtotal Result
formula
=SUBTOTAL(9,C3:C5)
=SUBTOTAL(9,C8:C9)
output
![](https://www.helpxel.com/wp-content/uploads/2021/10/Sub_result2.jpg)
Step Two: AGGREGATE
chosen_action: We will choose to sum our values, function number 9 (function_num)
=SUBTOTAL(sum our values
![](https://www.helpxel.com/wp-content/uploads/2021/10/Agg_func.jpg)
what_values_to_ignore: We will choose to ignore any nested subtotals, option number 9(options)
=SUBTOTAL(sum our values, ignore nested subtotals
![](https://www.helpxel.com/wp-content/uploads/2021/10/Agg_option.jpg)
given_range: Our range we want to sum (array)
=SUBTOTAL(sum our values, ignore nested subtotals, the range to sum)
![](https://www.helpxel.com/wp-content/uploads/2021/10/Agg_array.jpg)
Result
formula
=AGGREGATE(9,0,C3:C10)
output
![](https://www.helpxel.com/wp-content/uploads/2021/10/SubAgg_result.jpg)
And there you have it, totals and subtotal independent of each other!