# Formula to AutoSum a Column Range, but Ignore Subtotals

#### wazzulu1

##### Board Regular
Hi;

I'm working with a huge spreadsheet that some other people worked on.

They have sorted and grouped a list of customers, and they have inserted subtotals for customers with multiple references.

I need to finalize the sheet by summing up the revenue figures, but due to the subtotals being inserted already, it inflates the totals if I simply use the autosum button on the tool bar for the designated cell.

Is there a way to create a formula that will total all the itemized detail, while ignoring the group subtotals without having to edit it maually?

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### texasalynn

##### Well-known Member
the subtotal function does that, so add a grand subtotal of the entire range.

#### wazzulu1

##### Board Regular
If I add a Grand Total formula, it will only pick up the numbers from the subtotals, not including details that are not sub totaled.

Every item has not been sub totaled, only those with multiple references.

This is where I'm stuck at trying to work around this.

#### texasalynn

##### Well-known Member
so it you put in the formula

Code:
``=subtotal(9,c2:c1000)``

of course change to your range is should total everything in the range area, including those that are not in a subtotal area. Subtotal knows to not include the additional subtotal lines. If you want to test it, do a small sampling

#### wazzulu1

##### Board Regular

This still added the sub totals in the figure.

I went through the column, and added the entries, and the total was the combined figure of the details plus the sub totals using the formula you supplied.

I see that the totals that have been grouped together have a =sum(), so this is why the code did not work.

Is there a way to work around this, to add up the details, and not those fields with a =sum() in them?

Try...

=SUM(Range)/2

Hope this helps!

#### wazzulu1

##### Board Regular

I tried this, but every row does not have a sumarized total, so the figure is still off considerably.

#### texasalynn

##### Well-known Member
would suggest you replace those that have SUM() with subtotal

an easy way to do this is find/replace
find=Sum(
replace=Subtotal(9,

#### wazzulu1

##### Board Regular
That was a very good suggestion, and I'm running with it!

Thank you so much for helping me with this.

Replies
23
Views
1K
Replies
2
Views
46
Replies
8
Views
431
Replies
1
Views
111
Replies
5
Views
420