Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Stupid question of the day alert!!

    I have a filtered list amongst which are 4 columns account name, to, from and revenue. When I choose account name I would like to see a list and a total of the revenue column.

    In the main list I have tried autosum in the last revenue cell and have tried =SUM(G2:G2749) when when I filter no total shows.

    Please help put a buffoon out of his misery.
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Insert (at least) two rows above the field names (column headers) of your data table, and place the sum formula above the data table with a blank row in between.

    BTW, the SUM function will add all cells, whether they are filtered or not. To add only the filtered cells, use SUBTOTAL:

    =SUBTOTAL(9,G4:G2751)

    (The formula takes two inserted rows into account)

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    Insert (at least) two rows above the field names (column headers) of your data table, and place the sum formula above the data table with a blank row in between.

    BTW, the SUM function will add all cells, whether they are filtered or not. To add only the filtered cells, use SUBTOTAL:

    =SUBTOTAL(9,G4:G2751)

    (The formula takes two inserted rows into account)
    Thanks Hans

    Right on the money as usual

    May I ask though what the 9 is in the formula?
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The 9 is the Function Number for SUM

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    SUBTOTAL can be used to aggregate data in several ways. The first argument specifies the operation to be used:
    1 = AVERAGE
    2 = COUNT
    ...
    9 = SUM
    etc.

    See the Excel help or SUBTOTAL.


  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    SUBTOTAL can be used to aggregate data in several ways. The first argument specifies the operation to be used:
    1 = AVERAGE
    2 = COUNT
    ...
    9 = SUM
    etc.

    See the Excel help or SUBTOTAL.
    Thanks for the explaination Hans that is a lot clearer
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

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