Subtotal function
Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Subtotal function

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I know that I can use the data/subtotal function to subtotal columns of information. Can i have the subtotal lines placed on a separate worksheet as opposed to grouped at the bottom of each section of data? Thanks

  2. #2
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi fredlef:
    I don't see why not. Below is a simple example where the Subtotal function has been used outside the original table. I guess what you are really doing in such a case is practically emulating the sum function -- unless I am missing something here!

    name hours
    Joe 40
    Joe 42
    Joe 46
    Cathy 50
    Cathy 32
    Bob 48
    Bob 36
    Bob 54


    Joe's SUBTOTAL =SUBTOTAL(9,B2:B4) 128
    Cathy's SUBTOTAL =SUBTOTAL(9,B3:B4) 82
    Bob's SUBTOTAL =SUBTOTAL(9,B7:B9) 138


  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Florida
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If your data is on sheet1 and you want your subtotals on sheet2 put the following on sheet2.

    =subtotal(9,sheet1!a1:a3)

    adjust cells as needed.


    Denny

    [ This Message was edited by: kinkyparamour on 2002-03-24 14:56 ]

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,335
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    You could consider Database function,
    Data Filter, or sumproduct.

    I added a column for Last Name in case all names not unique and I used Named Ranges for the information. You can use cell addresses if you want.

    =SUMPRODUCT((rN1=E3)*(rN2=F3)*(rHrs))

    With criteria First Name and Last Name in
    E3 and F3 you secure result for 1st person.
    Copy the formula down.

    If you do not want the Last Name part just delete that part.

    Notes:
    - data does not have to be sort by name
    - the formula will aggregate any number of
    amounts that meet the criteria

    [ This Message was edited by: Dave Patton on 2002-03-24 15:53 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com