Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Jul 2005
    Location
    Earth, New Jersey, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subtotal (MS Excel 2007)

    I need to sum total for outstanding balance at each change in Customer and drill down the customer by ship-to in order to get the sub-totals for the ship-to's outstanding bal. I've attached a test file. Col "B" (lookup name) Col C "(Ship-to). Col "E" (outstanding bal). The ship-to's are tied to the LookupName (or acct# for a better explanation). First I would like to sub total the outstanding bal ("E") for the Ship-to ("C"), if it exist. Then have an sum total for Customer's ("A') account. If no Ship-to's exist for Customer, then just total customer's "outstanding bal" ("E"). I want to "Bold" the subtotals and overall totals per account. Sum subtotal & overall in "E" (outstanding bal). Thank you for any assistance you can provide.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subtotal (MS Excel 2007)

    There's only one customer and one Lookup Name in your sample sheet, so we can only create subtotals on the Ship To column. See attached version, and see Excel 2007: Calculating Subtotals for a description of how to create subtotals in Excel 2007.
    Attached Files Attached Files

  3. #3
    Star Lounger
    Join Date
    Jul 2005
    Location
    Earth, New Jersey, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotal (MS Excel 2007)

    Hi Hans!

    When I created the sheet, I thought about that and I added several other account #s to the sheet that did not have ship-to's, I thought I saved the updates before attaching but apparently I didn't and for that I apologize. Here's the spreadsheet with accounts that don't have ship-to's. Thank you so much <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subtotal (MS Excel 2007)

    Does the attached workbook do what you want? I added a column SubTotalCol, and in F2 entered the formula

    =IF(C2="",A2,C2)

    and filled down.
    I added subtotals breaking on SubTotalCol first, then on Customer #.
    Attached Files Attached Files

  5. #5
    Star Lounger
    Join Date
    Jul 2005
    Location
    Earth, New Jersey, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotal (MS Excel 2007)

    YES! You are truly the GREATEST! Thank you so much!
    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  6. #6
    Star Lounger
    Join Date
    Jul 2005
    Location
    Earth, New Jersey, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotal (MS Excel 2007)

    Hi Hans!
    I think I maybe missing a step. I followed your directions but I'm not getting the same values that you have in the attachment. This is what I've done. Inserted a column SubTotal Col, in row F2 entered the formula =IF(C2="",A2,C2) . Then I click "SubTotal" Icon in Excel, first @ Each Change in "SubTotal Col" . Use Function "Sum" . Add Subtotal to "SubTotal Col". I leave the following boxes checked. "Replace Current Subtotals" . "Summary Below Data". Then I click "Sub Total" icon again and then I select @ Each Change in "Customer". Use Funtion "Sum". Add Subtotal to "SubTotal Col". Am I doing this correctly or am I missing something. Thank you

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subtotal (MS Excel 2007)

    For the second subtotal, you should CLEAR the check box "Replace Current Subtotals", otherwise the subtotals on Customer will replace those on Sub Total Col.

  8. #8
    Lounger
    Join Date
    Aug 2008
    Location
    Daytona Beach, Florida, USA
    Posts
    42
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Thumbs up

    Is there a way to Bold/italized the Subtotals row including the totals not just the description

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    See Formatting Subtotal Rows (Tips.Net) for several options.

  10. #10
    Lounger
    Join Date
    Aug 2008
    Location
    Daytona Beach, Florida, USA
    Posts
    42
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Exclamation

    Thank you, You are a life saver, if wasn't for this website and a Access teacher telling me about it, I don't know where I would have found that.

Posting Permissions

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