Formula to AutoSum a Column Range, but Ignore Subtotals

wazzulu1

Board Regular
Joined
Oct 4, 2006
Messages
161
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?
 

Some videos you may like

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
Joined
May 19, 2002
Messages
8,458
the subtotal function does that, so add a grand subtotal of the entire range.
 

wazzulu1

Board Regular
Joined
Oct 4, 2006
Messages
161
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
Joined
May 19, 2002
Messages
8,458
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
Joined
Oct 4, 2006
Messages
161

ADVERTISEMENT

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?

I appreciate your help.
 

wazzulu1

Board Regular
Joined
Oct 4, 2006
Messages
161

ADVERTISEMENT

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

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
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
Joined
Oct 4, 2006
Messages
161
That was a very good suggestion, and I'm running with it!

Thank you so much for helping me with this.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,330
Messages
5,528,043
Members
409,799
Latest member
mlewan_ca

This Week's Hot Topics

Top