Results 1 to 2 of 2
  • Thread Tools
  1. 3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    subtotals (2002)

    what's the best way to get subtotals when the column uf numbers is interrupted by blank rows?
    (The blank rows correspond to "major" headings for which I would like to have subtotals.)

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    10,932
    Thanks
    11
    Thanked 280 Times in 274 Posts

    Re: subtotals (2002)

    I am not completely sure I understand your setup.

    1) Have you tried using the "built-in" data-subtotals feature in excel? it will place subtotals automatically between the "categories" that you pick.

    But to try and answer your question. If I understand it:
    Let's assume you have numbers in column A, and a header in row 1 some of the rows in column A are blank to indicated where you want subtotals.
    I will assume you want the subtotals in column B, and you want to total col a from the row above the blank to the previous blank row.
    Put this formula in B2:

    <pre>=IF(ISBLANK(A2),SUBTOTAL(9,A$2:A2)-SUM(B$1:B1),"")</pre>


    This can be copied down the rows
    So, if col A is not blank you get a null string. If it is blank it sums the values from the first row to the current row then subtracts the sum of the row above the current row to the first row, giveing you the subtotal of the rows above.

    If you want the subtotal to sum the values in column A for the rows below th blank row and to the next blank row (ie the subtotal as a "heading" above the data) you can use this formula (change the A100 and B100 to the last row)

    <pre>=IF(ISBLANK(A2),SUBTOTAL(9,A2:A$100)-SUM(B3:B$100),"")</pre>


    The logic is the same only it sums from the bottom instead of the top.
    In either case change the column letters as desired.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •