Forum Discussion

Rawle Mars's avatar
Rawle Mars
Copper Contributor
Aug 05, 2020
Solved

Subtotals and Grand totals

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 ...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 05, 2020

Rawle Mars 

Please try in B186

=SUBTOTAL(109,B2:B181)
  • Rawle Mars's avatar
    Rawle Mars
    Copper Contributor
    Aug 06, 2020

    SergeiBaklan 

     

    Yes that formula worked, thanks very much.

  • Poogermum's avatar
    Poogermum
    Brass Contributor
    Aug 05, 2020

    iSergeiBaklan 

     

    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...

     

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Aug 06, 2020

      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. 

Resources