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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

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
2
Views
423
Replies
23
Views
2K
Replies
5
Views
413
Replies
5
Views
347
Replies
2
Views
224

1,141,139
Messages
5,704,515
Members
421,353
Latest member
jekoxien15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back