# Thread: Subtotal (MS Excel 2007)

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

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

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

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

5. ## 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. ## 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. ## 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. Is there a way to Bold/italized the Subtotals row including the totals not just the description

9. See Formatting Subtotal Rows (Tips.Net) for several options.

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