Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    subtotals (excel 2002)

    i have a series of figures whichi want to subtotal
    column "A" holds an account no. which changes column "D" holds the set of figures which i require a subtotal for. due to the fact that the last colum of the database is some 30 columns away .
    can anyone help with a way to place the subtotal in colum "AK"

  2. #2
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: subtotals (excel 2002)

    I'm not quite sure what you're after but XL has a built in feature for sub-totals the navigation is Data>Subtotals. You can sum, count and etc..based on the criteria you select.

    John

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: subtotals (excel 2002)

    yes and easy to use but try to get the subtotal in another colum than wehere microsoft want to put itsubtotal should be in column "F" i wish to place it in column "K"

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

    Re: subtotals (excel 2002)

    1. You could put links to column D in column AK (AK1 contains the formula =D1, etc.), and create subtotals in column AK.

    2. You could create subtotals in column D, and put links to column D in column AK.

    If this is not what you want, can you explain in more detail?

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: subtotals (excel 2002)

    I'm a little confused.
    I was able to place a subtotal in whatever column I wanted:
    Data- subtotal -
    At each change: [check "A"]
    Use function: [Sum]
    If you want the subtotal in K check "K" in the "add Subtotal to" box
    If you want AK, check AK.

    If you get a little more specific about what you have and what you want, I think we can come up with a solution.

    Steve
    It is unclear to me what you want that you can't get.

  6. #6
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: subtotals (excel 2002)

    yes i agree, but whilst the sub total colum "D" would automatically be placed in the "D" colum. I want to place in colum "F" the resulting value

  7. #7
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: subtotals (excel 2002)

    i have three columns A B C
    colum a holds "A" holds an account no.
    colum b holds an invoiced ammount
    i am sub totaling column B . if coolum B has ammounts varying between 10 and 1500 and i wish to subtotal i might get a subtotal of 1510.
    in colum "C" i have to check and see if the value is between 1500 and 2000, and copy to a separate sheet. in this case the value of the subtotal is 1510 it could quite easily be 3500 but i would pick up the value of 1500 from colum "B" where as i only want the subtotals and not the individual values.

    A B C
    12 1500 yes
    12 10
    12 485

    subtotal 1995 yes

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: subtotals (excel 2002)

    How about this:
    Add this formula in F2(I asume F1 has the Header):

    <pre>=IF(AND(RIGHT(A2,5)="Total",RIGHT(A2,11)<>"Gr and Total"),D2,"")</pre>

    and copy it down the column.

    It will put a null string ("") or the subtotal from column D in column F.
    Steve

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: subtotals (excel 2002)

    I am still totally confused about what you have and what you want.
    Could you post a simple example of what you have
    and also an "after" of what you want it to look like.

    If you want to only extract subtotals, you can look in col A for the "Total" or the "Grand Total" as I did and then look at the values.

    A pivot table might also summarize the data for you without going through the subtotal scheme. SumIFs or array formulations might also get you the values you want to extract without this work.

    What are you trying to ulimately do with the subtotals?
    Steve

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: subtotals (excel 2002)

    If I understand correctly, you want the subtotals for column D to appear in Column F. Excel's Data Subtotals function will not do that automatically, it always uses the same column for subtotals as the column containing the data. However, you can do what you want with some extra manual steps, provided column F is empty: create the subtotals in Column D using Data, Subtotals, then by using the outline (the numbers 1,2,3, etc. in boxes) buttons to the left of the A1 cell, show only the subtotals. Select the Subtotals in Column D, then select Edit, Goto Special, Visible Cells only. Then Cut and Paste the Subtotals from Column D to Column F.
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: subtotals (excel 2002)

    iam sorry i havn't replied sooner but my email server has been down.
    i include a short xl file for you

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: subtotals (excel 2002)

    Put this in D3 and copy it down:

    <pre>=IF(AND(RIGHT(A3,5)="Total",RIGHT(A3,11)<>"Gr and Total",C3>150,C3<200),"yes","")</pre>


    Steve

  13. #13
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: subtotals (excel 2002)

    everthing worked fine except for one small point pls read the attached file

    thanking you for all you have done so far

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: subtotals (excel 2002)

    Your formula is:
    =IF(AND(RIGHT(A3,5)="Total",RIGHT(A3,11)<>"Grand Total",C3>750,C3<650),"yes","")
    which states that you want >750 AND <650 which is NEVER TRUE.

    You want:
    =IF(AND(RIGHT(A3,5)="Total",RIGHT(A3,11)<>"Grand Total",C3>650,C3<750),"yes","")

    The grand total will ALWAYS be marked with a blank. You stated that you ONLY wanted subtotals. If the grand Total should be included (and checked for the range) just use:
    =IF(AND(RIGHT(A3,5)="Total",C3>650,C3<750),"yes"," ")
    Then BOTH "Total" and "Grand Total" will be TRUE (Grand total MUST ALSO be within the range!!) to be a yes. If you want subtotals within a range and a yes to grand total no matter what the range:
    =IF(OR(AND(RIGHT(A14,5)="Total",C14>650,C14<750),R IGHT(A14,11)="Grand Total"),"yes","")

    Steve

  15. #15
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: subtotals (excel 2002)

    thank you for all your help

    alex

Page 1 of 2 12 LastLast

Posting Permissions

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