Formula to AutoSum a Column Range, but Ignore Subtotals

[wazzulu1]

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?

 

[texasalynn]

the subtotal function does that, so add a grand subtotal of the entire range.

 

[wazzulu1]

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]

so it you put in the formula

=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]

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.

 

[Domenic]

Try...

=SUM(Range)/2

Hope this helps!

 

[wazzulu1]

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

 

[texasalynn]

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]

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

Thank you so much for helping me with this.