Results 1 to 3 of 3

Thread: Functions

  1. #1
    New Lounger
    Join Date
    Oct 2009
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello All,

    I use Excel 2000 in MS Window XP environment.

    I have attempted to use the subtotal function with no luck at all. I need to subtotal Area_m2 for given individual land use codes (Code_00) by Site_ID. There are about 1500 sites. Here is a small sample of a 25000 line spreadsheet. I would be most grateful for any help.

    Thank you.

    Code:
    Code_00	Area_m2	Site_ID
    523	2045274.799	13
    423	1721366.983	13
    331	690261.0459	13
    211	99617.69994	13
    211	242323.193	 13
    311	105549.2516	14
    311	144214.8964	14
    112	2320853.269	14
    142	39155.7351	 14
    211	782010.8421	14
    211	21017.82888	3
    231	6851.292606	3
    242	18795.47182	3
    311	1877510.184	3
    322	208.3819602	3
    242	504564.2424	3
    242	17925.44181	3
    211	1198319.595	3
    242	247053.0404	3

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Welcome to the Lounge!

    For subtotals to work, the data must already be grouped by the Site_ID and Code_00 columns; the easiest way to do this is:
    Click anywhere in the data table.
    Select Data | Sort...
    Select Site_ID in the first dropdown box, and Code_00 in the second dropdown box.
    Click OK.

    Then select Data | Subtotals...
    Select Site_ID in the "At each change in" dropdown box.
    Leave the "Use function" dropdown box set to Sum.
    Tick the check box for Area_m2 and clear the others in the "Add subtotal to" section.
    Click OK.

    Select Data | Subtotals... again.
    Select Codw_00 in the "At each change in" dropdown box.
    Leave the "Use function" dropdown box set to Sum.
    Tick the check box for Area_m2 and clear the others in the "Add subtotal to" section.
    IMPORTANT: clear the "Replace current subtotals" check box!
    Click OK.

    You should now have subtotals by Site_ID and within that by Code_00.

    Alternatively, you could create a pivot table based on your data table.

  3. #3
    New Lounger
    Join Date
    Oct 2009
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you so much, Hans!
    Roland

Posting Permissions

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