» Adding Subtotals to Primary and Secondary Fields
CATEGORY - Excel Subtotals
VERSION - All Microsoft Excel Versions
1. Sort, in ascending order, the field for which you want to have the secondary subtotal. In this example (see screenshot), Customer Name is the secondary field.
2. Sort, in ascending order, the field for which you want to have the primary subtotal. In this example (see screenshot), Market is the primary field.
3. Click the Subtotals box.
4. From the At each change in dropdown list, select Market.
5. In Add Subtotal to, select the Quantity and Income checkboxes.
6. Deselect the Replace current subtotals checkbox, and then click OK.
7. Repeat steps 3 and 4, and select Customer Name.
8. Click OK.
Book Store:
Recommended Books:
- Rich Dad, Poor Dad: What the Rich Teach Their Kids About Money--That the Poor and Middle Class Do Not!
- Financial Statements: A Step-By-Step Guide to Understanding and Creating Financial Reports
- F1 Get the Most out of Excel! The Ultimate Excel tip Help Guide
- Your First Business Plan: A Simple Question and Answer Format Designed to Help You Write Your Own Plan (3rd Ed)
- The Accounting Game : Basic Accounting Fresh from the Lemonade Stand
- Mortgages For Dummies®
Potential problem with multiple Subtotals
Guest Posted on: 31-12-1969
I'm not sure if this is a bug in Excel, or just something that was never looked into, but using subtotals more than once, as in the example, does not always produce the desired results. For instance, using the example data, if the Market 'Africa' only has data for the Customer Name 'AIG', then Customer Name 'AIG' will not subtotal for 'Africa', but instead it will subtotal for 'Asia'. Also, in the opposite scenario, if the Market 'Asia' only has data for the Customer name 'Pacific Bell', then 'Pacific Bell' will not subtotal under 'Africa', but instead under 'Asia'. In other words, Excel will only force a subtotal when there is a change in the 'At each change' field. If this field is a sceondary subtotal and it is consecutive between primary subtotals, then excel will wait til the secondary filed has changed before it will force a subtotal, even if this overlaps two primary subtotals.Excel Fan
Henry Zheng Posted on: 31-12-1969
You have to pay attention to the order when you use subtotal. e.g., subtotal by Market first and then by CustomerExcel Subtotal
Amanjit Posted on: 31-12-1969
I need to calculate UPTime from the ExcelI need to know is icon total can calculate time. Reply: Excel Subtotal - Amanjit from Malaysia wrote on August 14, 2003 12:49 AM EST
Alan Posted on: 31-12-1969
Hi Amanjit,
If I understand your query correctly, you are asking whether the subtotal feature can be used with times.
The answer is yes it can.
Dates and times are simply numeric values (date / time values), and can be added or subtracted like any other numbers.
For example, your post was at August 14, 2003 12:49 AM (ingoring the EST notation - I have no idea what that means!)
Excel's date / time value for that is:
37847.53402777...
This means 37,847 days since 1 Jan 1900 and 0.53402777... of a day (just past midday).
Hope that helps.
Alan.
Reply: Error immediately above
Alan Posted on: 31-12-1969
Hi,
I made a mistake above.
Your post was at 12:49AM (not PM), so the date / time value is:
37847.0340277778
Same number of days as before, but just past midnight.
Apologies for any confusion!
Alan.
Excel Formula and Function
Excel Date and Time
Excel Macros and VBA
Excel Text, Editing and Format
Excel General
Excel Data
