Subtotals and Grand totals

[Rawle Mars]

I have numbers that range from B3 to B53 and a subtotal =subtotal(9,b3:b53) in B54.  I have another set of numbers from B58 to B180 and a subtotal =subtotal(9,b58:b180) in B181.  In B 186 I have the formula =sum(B2:B181)  but the total figure returned is including both the subtotals and the the individual data that makes up the subtotals so the result is double the correct total.  Please for some help on this issue.

[#2 · Accepted solution]

@Rawle Mars 

Please try in B186

=SUBTOTAL(109,B2:B181)

[#3]

=SUMPRODUCT((ISFORMULA(B3:B180)=FALSE)*B3:B180)

found it here
https://stackoverflow.com/questions/55803385/sumif-cells-are-not-formulas

[#4]

i@Sergei Baklan 

Hi Sergei

Reading your answer to expand my knowledge of excel. How come excludes subtotals when using the hidden cells value? Are all calculated values considered a 'hidden value'? Bit confused...

[#5]

@Rawle MarsHave you considered adding a Pivot table or a summary table on a separate sheet to make it easier to look at the data instead of having subtotals and grand totals on the same sheet?

[#6]

@Sergei Baklan 

Yes that formula worked, thanks very much.

[#7]

@Poogermum 

Sorry for the confusion. What to use, 9 or 109 depends on your scenario, both work the same way if you have no hidden rows - formula ignores any other SUBOTOTAL within sum range. Just in my practice I more often use 109 to ignore manually hided rows in sum. Again, if no such it doesn't matter which parameter to use.

Another trick, if no numbers in between the ranges which shall not be summed, and no need to work with hided rows, you may use simple SUM instead of SUBTOATL. If now you SUBTOATL range1, range2, range3 and after that SUBTOTAL on entire range to ignore intermediate ones, you may use SUM() on each of ranges and for the grand total SUM(entire range)/2.